Chapter 17
Aggregation Function
“Teradata climbed the Aggregate Mountain and delivered a better way to Sum It.”
Table of Contents Chapter 17 – Aggregation Function
– Quiz – You calculate the Answer Set in your own Mind
– Answer – You calculate the Answer Set in your own Mind
– Quiz – How many rows come back?
– GROUP BY when Aggregates and Normal Columns Mix
– GROUP BY Delivers one row per Group
– GROUP BY Dept_No or GROUP BY 1 the same thing
– Limiting Rows and Improving Performance with WHERE
– WHERE Clause in Aggregation limits unneeded Calculations
– Keyword HAVING tests Aggregates after they are Totaled
– Keyword HAVING is like an Extra WHERE Clause for Totals
– Three types of Advanced Grouping
– Final Answer to Test Your Knowledge on Aggregates
Quiz – You calculate the Answer Set in your own Mind
Aggregation_Table
| Employee_No | Salary |
| 423400 | 100000.00 |
| 423401 | 100000.00 |
| 423402 | NULL |
SELECT AVG(Salary) as “AVG”
,Count(Salary) as SalCnt
,Count(*) as RowCnt
FROM Aggregation_Table ;
What would the result set be from the above query? The next slide shows answers!
Answer – You calculate the Answer Set in your own Mind
Aggregation_Table
| Employee_No | Salary |
| 423400 | 100000.00 |
| 423401 | 100000.00 |
| 423402 | NULL |
SELECT AVG(Salary) as “AVG”
,Count(Salary) as SalCnt
,Count(*) as RowCnt
FROM Aggregation_Table ;
What would the result set be from the above query? The next slide shows answers!
The 3 Rules of Aggregation
Aggregation_Table
| Employee_No | Salary |
| 423400 | 100000.00 |
| 423401 | 100000.00 |
| 423402 | NULL |
SELECT AVG(Salary)
,Count(Salary)
,Count(*)
FROM Aggregation_Table;
1) Aggregates Ignore Null Values.
2) Aggregates WANT to come back in one row.
3) You CAN'T mix Aggregates with normal columns unless you use a GROUP BY.
AVG(Salary) = $100000.00
Count(Salary) = 2
Count(*) = 3
There are Five Aggregates
Aggregation_Table
| Employee_No | Salary |
| 423400 | 100000.00 |
| 423401 | 100000.00 |
| 423402 | NULL |
There are FIVE AGGREGATES which are the following:
MIN – The Minimum Value.
MAX – The Maximum Value.
AVG – The Average of the Column Values.
SUM – The Sum Total of the Column Values.
COUNT – The Count of the Column Values.
Quiz – How many rows come back?
How many rows will the above query produce in the result set? The answer is one.
Troubleshooting Aggregates
How many rows will the above query produce in the result set? This query errors!
GROUP BY when Aggregates and Normal Columns Mix
GROUP BY Delivers one row per Group
Group By Dept_No command allow for the Aggregates to be calculated per Dept_No.
GROUP BY Dept_No or GROUP BY 1 the same thing
SELECT Dept_No
,MIN (Salary)
,MAX (Salary)
,SUM (Salary)
,AVG (Salary)
,Count(*)
FROM Employee_Table
GROUP BY Dept_No
ORDER BY Dept_No;
Both Queries are the same to the system.
SELECT Dept_No
,MIN (Salary)
,MAX (Salary)
,SUM (Salary)
,AVG (Salary)
,Count(*)
FROM Employee_Table
GROUP BY 1
ORDER BY 1;
Both queries above produce the same result. The GROUP BY allows you to either name the column or use the number in the SELECT list, just like the ORDER BY.
Limiting Rows and Improving Performance with WHERE
Will Dept_No 300 be calculated? Of course you know it will…NOT!
WHERE Clause in Aggregation limits unneeded Calculations
The system eliminates reading any other Dept_No's other than 200 and 400. This means that only Dept_No's of 200 and 400 will come off the disk to be calculated.
Keyword HAVING tests Aggregates after they are Totaled
Previous Answer Set
The HAVING Clause only works on Aggregate Totals. The WHERE filters rows they don't want calculated, but the HAVING filters the Aggregate totals after the calculations, thus eliminating certain Aggregate totals.
Keyword HAVING is like an Extra WHERE Clause for Totals
Previous Answer Set
NEW Answer Set
The HAVING Clause only works on Aggregate Totals and only Count(*) > 2 can return.
Three types of Advanced Grouping
Not all rows in this table are displayed
Sales_Table
| Product_ID | Sale_Date | Daily_Sales |
| 1000 | 2000-09-28 | 48850.40 |
| 2000 | 2000-09-28 | 41888.88 |
| 3000 | 2000-09-28 | 61301.77 |
| 1000 | 2000-09-29 | 54500.22 |
| 2000 | 2000-09-29 | 48000.00 |
| 3000 | 2000-09-29 | 34509.13 |
| 1000 | 2000-09-30 | 36000.07 |
| 2000 | 2000-09-30 | 49850.03 |
| 3000 | 2000-09-30 | 43868.86 |
| 1000 | 2000-10-01 | 40200.43 |
| 2000 | 2000-10-01 | 54850.29 |
| 3000 | 2000-10-01 | 28000.00 |
| 1000 | 2000-10-02 | 32800.50 |
| 2000 | 2000-10-02 | 36021.93 |
| 3000 | 2000-10-02 | 19678.94 |
GROUP BY Grouping Sets
GROUP BY Rollup
GROUP BY Cube
GROUP BY GROUPING Sets
GROUP BY GROUPING Sets above will show you what your Daily_Sales were for each Product_ID, for each month, and for each year.
GROUP BY Rollup
Not all 16 rows in this answer set could be displayed
GROUP BY ROLLUP displays Daily_Sales were for each Product_ID, for each distinct month, for each month per year and for each year, plus a grand total.
GROUP BY Rollup Result Set
This is the full result set from the previous GROUP BY ROLLUP query.
GROUP BY Cube
Not all 24 rows in this answer set could be displayed
GROUP BY ROLLUP displays Daily_Sales were for each Product_ID, for each distinct month, for each month per year and for each year, plus a grand total.
GROUP BY CUBE Result Set
Testing Your Knowledge
First of all, SELECT the AVERAGE Salary from the Employee_Table.
Testing Your Knowledge
Now , SELECT the AVERAGE Salary and the SUM of the Salary from the Employee_Table.
Testing Your Knowledge
Now , SELECT the AVERAGE Salary and the SUM of the Salary from the Employee_Table but PER DEPARTMENT(Dept_No).
Testing Your Knowledge
After that, SELECT the AVERAGE Salary and the SUM of the Salary from the Employee_Table but PER DEPARTMENT(Dept_No). However, I only want to see the people from Department 200, 300, 400.
Testing Your Knowledge
After that, SELECT the AVERAGE Salary and the SUM of the Salary from the Employee_Table but PER DEPTMENT(Dept_No). However, I only want to see Department 200, 300, 400 which has an AVERAGE Salary of over 43,000.
Final Answer to Test Your Knowledge on Aggregates
Answer Set
| Dept_No | AVG(Salary) | Sum(Salary) |
| 200 | 44944.44 | 89888.88 |
| 400 | 48316.67 | 144950.00 |
Select Dept_No, AVG(Salary), SUM(Salary
From Employee_Table
Where Dept_No IN (200, 300, 400)
Group By Dept_No
Having AVG(Salary) > 43000
This should be your final answer set. The query under it should be approximately what you wrote to attain such an answer set. How'd you do?