Lesson 13. Grouping And Summarizing Data

In the section on aggregate functions, all of the examples ran operations on the entire table. While that is sometimes useful information, executives usually want to see totals of things by logical groups.

For example, an executive may want to see total sales by all the stores in the organization. This is where GROUP BY and HAVING come into play.

GROUP BY the columns you want to group on.

HAVING the conditions you specify.

A peculiar rule of GROUP BY is you can specify any column in the table in the GROUP BY clause. Having a select statement that is nothing but the aggregate value is totally legal. But you can NOT have a column in your SELECT clause that is NOT in your GROUP BY clause.

The difference between HAVING and WHERE is that HAVING is for specifying conditions on the aggregate value. So, you can say give me all the average values grouped by store where the average is greater than some arbitrary value.

Examples

Three Examples Of Grouping And Summarizing Data

USE AdventureWorks2016

--this is valid SQL
SELECT SUM(LineTotal)
FROM Sales.SalesOrderDetail
GROUP BY ProductID

--this is a syntax violation
SELECT UnitPrice, SUM(LineTotal)
FROM Sales.SalesOrderDetail
GROUP BY ProductID

SELECT ProductID, SUM(LineTotal) AS SumOfOrders
FROM Sales.SalesOrderDetail
GROUP BY ProductID
HAVING SUM(LineTotal) > 100000