Chapter 11 – Aggregation
“Greenplum climbed Aggregate Mountain and delivered a better way to Sum It.”
- Tera-Tom Coffing
Quiz – You calculate the Answer Set in your own Mind
SELECT Avg(Grade_Pt) AS "AVG"
,Count(Grade_Pt) AS "Count"
,Count(*) AS "Count *"
FROM Student_Table
WHERE Class_Code IS NULL
AVG Count Count *
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
SELECT Avg(Grade_Pt) AS "AVG"
,Count(Grade_Pt) AS "Count"
,Count(*) AS "Count *"
FROM Student_Table
WHERE Class_Code IS NULL
Here are your answers!
Quiz – You calculate the Answer Set in your own Mind
Aggregation_Table
Employee_NoSalary
__________________________
423400100000.00
423401100000.00
423402NULL
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
SELECT AVG(Salary) as "AVG"
,Count(Salary) as SalCnt
,Count(*) as RowCnt
FROM Aggregation_Table ;
Here are your answers!
The 3 Rules of Aggregation
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.
There are Five Aggregates
The five aggregates are listed above.
Quiz – How many rows come back?
How many rows will the above query produce in the result set?
Answer – How many rows come back?
How many rows will the above query produce in the result set? The answer is one.
Troubleshooting Aggregates
If you have a normal column (non-aggregate) in your query, you must have a corresponding GROUP BY statement.
GROUP BY when Aggregates and Normal Columns Mix
If you have a normal column (non-aggregate) in your query, you must have a corresponding GROUP BY statement.
GROUP BY delivers one row per Group
The “Group By Dept_No” command allows for the Aggregates to be calculated per Dept_No. The data has also been sorted with the ORDER BY statement.
GROUP BY Dept_No or GROUP BY 1 the same thing
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
The HAVING Clause only works on Aggregate Totals. The WHERE filters rows to be excluded from calculation, but the HAVING filters the Aggregate totals after the calculations, thus eliminating certain Aggregate totals.
Aggregates Return Null on Empty Tables
When an aggregate is run against an empty table it returns a null value.
Keyword HAVING is like an Extra WHERE Clause for Totals
The HAVING Clause only works on Aggregate Totals, and in the above example, only Count (*) > 2 can return.
Keyword HAVING tests Aggregates after they are totaled
The HAVING Clause only works on Aggregate Totals. The WHERE filters rows to be excluded from calculation, but the HAVING filters the Aggregate totals after the calculations, thus eliminating certain Aggregate totals.
Getting the Average Values per Column
The first query retrieved the average rows per value for the column Product_ID. The example below did the same, but for the column Sale_Date.
Three types of Advanced Grouping
There are three advanced grouping options:
GROUP BY Grouping Sets
GROUP BY Rollup
GROUP BY Cube
SELECT Product_ID
,EXTRACT (MONTH FROM Sale_Date) AS MTH
,EXTRACT (YEAR FROM Sale_Date) AS YR
,SUM(Daily_Sales) AS SUM_Daily_Sales
FROM Sales_Table
GROUP BY GROUPING SETS (Product_ID, MTH, YR)
ORDER BY Product_ID Desc, MTH Desc, YR Desc;
Be prepared to be amazed. There are three advanced options listed above for grouping data. Each is more powerful that the one before. The next pages will give great example.
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
GROUP BY ROLLUP displays what the 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
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
GROUP BY CUBE Result Set
In Nexus, just right click on the Sales_Table and choose the Super Join Builder. Select all the columns and choose the Analytics tab on the top right. In the Analytics Tab choose Grouping Sets. Then drag the Product_ID column to the Product, drag the Sale_Date to the Date Column, and then drag the Daily_Sales column to the Sum. The last step is to make sure you place a check mark in all the “Group By” Functions on the right of the screen and then you are ready to hit Execute. You can also send the SQL to Nexus using the Send SQL to Nexus button.
Quiz - GROUP BY GROUPING SETS Challenge
Write SQL that will perform a Group by Grouping Sets. Your mission is to build a report that will show the Average Grade_Pt for three different sets. Those sets are by Class_Code, by Credits and by Course_ID. Good Luck!
The answer is on the next page.
Answer To Quiz - GROUP BY GROUPING SETS Challenge
SELECT Class_Code
,AVG(Grade_Pt)
,Credits
,sc.Course_Id
FROM Student_Table s,
Student_Course_Table sc,
Course_Table "c"
WHERE s.Student_Id=sc.Student_Id
AND sc.Course_Id="c".Course_Id
GROUP BY GROUPING SETS (class_code, sc.Course_ID, credits)
Above is something to enjoy and learn from.