The aggregate functions are used to summarize column data values stored in rows. Aggregates eliminate the detail information from the rows and only return the answer. Therefore, the result is one or more aggregated values as a single line or one line per unique value, as a group. The other characteristic of these functions is that they all ignore null values stored in column data passed to them.
The math aggregates are the original functions used to provide simple types of arithmetic operations for the data values. Their names are descriptive of the operation performed. The functions are listed below with examples following their descriptions. The newer, V2R4 statistical aggregates are covered later in this.
The SUM Function
Accumulates the values for the named column and prints one total from theaddition.
The AVG Function
Accumulates the values for the named column and counts the number of values added for the final division to obtain the average.
The MIN Function
Compares all the values in the named column and returns the smallest value.
The MAX Function
Compares all the values in the named column and returns the largest value.
The COUNT Function
Adds one to the counter each time a value other than null is encountered.
The aggregates can all be used together in a single request on the same column, or individually on different columns, depending on your needs.
The following syntax shows all six aggregate functions in a single SELECT to produce a single line answer set:
The following table is used to demonstrate the aggregate functions:
The next SELECT uses the Student table, to show all aggregates in one statement working on the same column:SELECT SUM(Grade_pt),AVG(Grade_pt),MIN(Grade_pt),MAX(Grade_pt),COUNT(Grade_pt) FROM Student_table; 1 Row Returned
Notice that Stanley's row is not included in the functions due to the null in his grade point average. Also notice that no individual grade point data is displayed because the aggregates eliminate this level of column and row detail and only returns the summarized result for all included rows. The way to eliminate rows from being included in the aggregation is through the use of a WHERE clause.
Since the name of the selected column appears as the heading for the column, aggregate names make for funny looking headings. To make the output look better, it is a good idea to use an alias to dress up the name used in the output. Additionally, the alias can be used elsewhere in the SQL as the column name.
The next SELECT demonstrates the use of alias names for the aggregates:1 Row Returned
Notice that when using aliases in the above SELECT they appear as the heading for each column. Also the words Total, Average and Count are in double quotes. As mentioned earlier, the double quoting technique is used to tell the PE that this is a column name, opposed to being the reserved word. Whereas, the single quotes are used to identify a literal data value.
Aggregates and Derived Data
The various aggregates can work on any column. However, most of the aggregates only work with numeric data. The COUNT function might be the primary one used on either character or numeric data. The aggregates can also be used with derived data.
The following table is used to demonstrate derived data and aggregation:
This SELECT totals the salaries for all employees and show what the total salaries will be if everyone is given a 5% or a 10% raise:
Notice that since both TITLE and FORMAT require parentheses, they can share the same set. Also, the AVG function and dividing the SUM by the COUNT provide the same answer.
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.