“Hadoop climbed Aggregate Mountain and delivered a better way to Sum It.”
- Tera-Tom Coffing
SELECTAvg(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!
SELECTAvg(Grade_Pt)AS "AVG"
,Count(Grade_Pt) AS "Count"
,Count(*) AS "Count *"
FROM Student_Table
WHERE Class_Code IS NULL
Here are your answers!
The five aggregates are listed above.
How many columns and rows will the above query produce in the result set?
How many rows will the above query produce in the result set? The answer is one.
If you have a normal column (non aggregate) in your query, you must have a corresponding GROUP BY statement.
If you have a normal column (non aggregate) in your query, you must have a corresponding GROUP BY statement.
If you have a normal column (non aggregate) in your query, you must have a corresponding GROUP BY statement.
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.
Will Dept_No 300 be calculated? Of course you know it will . . . NOT!
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.
The query retrieved the average rows per value for the column Product_ID.
The query above retrieved the average rows per value for both columns in the table.