Totals (WITH) Teradata

Compliance – Teradata Extension

Teradata provides for the creation and display of a final total for all the data values in one or more columns. At the same time, it shows the detail values, row by row, that went into the total. To obtain a grand total the SELECT contains a WITH to identify the column on which to perform an aggregate.

Because it is called a total, the SUM function is normally used, however, the other aggregates may also be used to generate a final value.

To produce a final total, use the following syntax:

The next SELECT shows the detail of the rows in the table with final total displayed at the end of the output report for the salary:

9 Rows Returnednext SELECT shows the detail of the rows

Since it is preferable not to see a label called SUM(salary), the subtotal needs to have a name or title associated with it. To do this, simply use the TITLE function and Teradata labels the newly created line using the text that is specified.

The above SELECT could be changed to the following:

9 Rows ReturnedSELECT could be changed to the following

Furthermore, it might be desirable to include an average salary to the previous example as seen in the next SELECT can accomplish this:

4 Rows Returnednext SELECT can accomplish this

As you can see in Teradata, it is relatively easy to obtain a final number for display from an aggregate, title it and format it, all this along with the detail row data that created it. Notice too that the totals can be formattedindividually and can be different.

Subtotals (WITHBY)

Compliance – Teradata Extension

As well as the ability to provide a total, Teradata can also provide one or more subtotals. To obtain a subtotal, the WITH incorporates a BY designation. The WITH…BY can be incorporated into existing SQL to provide a subtotal. When using subtotals, it is important to choose a control column. It must contain a value that groups data together and causes a break to occur when it changes. In addition, the value is also used to sort the data so that all breaks occur only once per unique value. At the break, the subtotal is displayed for the values up to that point and then reset for the next group of values. Unlike the GROUP BY, the WITH…BY performs a sort operation to with a default sequence of ASC order. However, if desired, the DESC can be used to request a descending sort.

The basic syntax of the WITH … BY and sample output follows:

The next SELECT uses the WITH…BY to add up the salaries within a department as one subtotal per department:

3 Rows Returnedsyntax of the WITH … BY

In the output, when the department number changed from 100 to 200 a break occurred. After the sort, all the department 100 rows are together and precede all of the rows for department 200. This break process repeats for as many unique values contained in the data.

The next SELECT demonstrates the combination of subtotals (WITH…BY) and total (WITH) in a single request:

3 Rows Returnedcombination of subtotals (WITH…BY)

Like any data value, when using BTEQ, the output can be formatted to dress up its appearance. This is requested in the WITH or WITH…BY.

Multiple Subtotals on a Single Break

Like the WITH, the WITH…BY may be used with more than one aggregate value on a single break. To accomplish this, simply add a comma after the previous aggregate and code the next.

The next SELECT adds an average along with the sum:

3 Rows Returnednext SELECT adds an average
next SELECT adds an average

In the above example, each time the value in the department number changes, both aggregates are displayed. Then, they are both reset and the next group of rows begins its own calculation.

All of these examples contain one column in the BY portion of the WITH … BY to monitor for changes. More than one column may be specified. However, be aware that as more columns are indicated, there is a higher probability that one of the values in the columns is going to change. When this happens, a break occurs and a subtotal is displayed. As a result, there will be more subtotals displayed.

Multiple Subtotal Breaks

With that said, it is likely that there is a need to have more than one subtotal in the request. Several WITH…BY subtotals can be incorporated into a single SQL statement. For each additional subtotal, a different column is used for the next subtotaled value from the list.
Since each WITH BY produces an ascending (lowest to highest values) sort, it is important to arrange them carefully to create the sequence desired in the output. The last WITH … BY in the SELECT statement is the major sort sequence. The first occurrence of WITH … BY is the most minor sort and any subtotal between them are also minor sorts, increasing in sort significance to the last WITH … BY.

The Sales table below is used to show the output with subtotal multiple breaks.

Multiple Subtotal Breaks

To code additional subtotals, simply add another WITH … BY as shown in the following SELECT submitted using BTEQ:

21 Rows ReturnedSELECT submitted using BTEQ
SELECT submitted using BTEQ

Since an explanation of what the subtotal represents is a good idea, it is common to add a TITLE to each subtotal. It dresses up the output instead of seeing the name of the aggregate with the <column-name>.

As a reminder of something mentioned earlier in this chapter, this capability does not work with ODBC and Queryman.

All rights reserved © 2018 Wisdom IT Services India Pvt. Ltd Protection Status

Teradata Topics