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 Returned
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 Returned
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 Returned
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.
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 Returned
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 Returned
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 Returned
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.
To code additional subtotals, simply add another WITH … BY as shown in the following SELECT submitted using BTEQ:21 Rows Returned
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.
Teradata Related Interview Questions
|Microstrategy Interview Questions||Informatica Interview Questions|
|MySQL Interview Questions||Oracle 11g Interview Questions|
|Hadoop Interview Questions||TeraData DBA Interview Questions|
|MYSQL DBA Interview Questions||Database Administration Interview Questions|
|DB2 SQL Programming Interview Questions||Hadoop Administration Interview Questions|
|Java Hadoop Developer Interview Questions||Informatica MDM Interview Questions|
|Informatica Admin Interview Questions||Hadoop Testing Interview Questions|
Teradata Related Practice Tests
|Microstrategy Practice Tests||Informatica Practice Tests|
|MySQL Practice Tests||Oracle 11g Practice Tests|
|Hadoop Practice Tests||TeraData DBA Practice Tests|
|MYSQL DBA Practice Tests||Database Administration Practice Tests|
|DB2 SQL Programming Practice Tests||Hadoop Administration Practice Tests|
Teradata Parallel Architecture
Fundamental Sql Using Select
On-line Help And Show Commands
Date And Time Processing
Character String Processing
Reporting Totals And Subtotals
Data Definition Language
All rights reserved © 2018 Wisdom IT Services India Pvt. Ltd
Wisdomjobs.com is one of the best job search sites in India.