Chapter 3 – Aggregation
“Oracle 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
FROMAggregation_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
FROMAggregation_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
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.
SELECT MIN (Salary)
,MAX (Salary)
,SUM (Salary)
,AVG (Salary)
,Count(*)
FROM Employee_Table ;
“Don’t count the days, make the days count.”
– Mohammed Ali
The five aggregates are listed above. Mohammed Ali was way off in his quote. He meant to say, "Don't you count the days, make the data count for you".
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
Error
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
Group By Dept_No command allow for the Aggregates to be calculated per Dept_No. The data has also been sorted with the ORDER BY statement.
GROUP BY Dept_No Works GROUP BY 1 Fails
Both queries above produce the same result on most systems, but Oracle fails when you use the column number with a GROUP BY statement. The ORDER BY allows you to either name the column or use the number in the SELECT list, but not the GROUP BY. You must use the column name in the GROUP BY when using Oracle.
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 to be excluded from calculation, 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
New Answer Set using the HAVING Statement
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
SELECT 'Product_ID' AS "Column Name"
,COUNT(*) / COUNT(DISTINCT(Product_ID)) AS "Avg Rows"
FROM Sales_Table ;
Column NameAvg Rows
______________________
Product_ID7
The query retrieved the average rows per value for the column Product_ID.
Average Values Per Column For all Columns in a Table
The query above retrieved the average rows per value for both columns in the table. The CAST command converted the data type to Decimal (5,2).