Aggregate Functions and GROUP BY

Examples are based on the GoGo Gadgets database

Aggregate Functions are calculations applied to all the records found from a SELECT command.

AVG(xxx) -- return the average value of specified field or expression
MAX(xxx) -- return largest value for specified field or expression
MIN(xxx) -- return smallest value for specified field or expression
SUM(xxx) -- return the total for specified field or expression
COUNT()  -- return number of records found

Example:

SELECT MIN(price), MAX(price)
FROM item

	MIN(price)	MAX(price)
1	2.0	39.0

Aliases

Aliases can be used to give more readable headings for columns.

SELECT MIN(price) AS "Cheapest Price", 
    MAX(price) AS "Dearest Price"
FROM item;

	Cheapest Price	Dearest Price
1	2.0	39.0

Aggregate and Non-Aggregate Expressions

Mixing non-aggregate and aggregate expressions in a SELECT statement is not permitted without Grouping

The query below is illegal, as it mixes non-aggregate productName with the aggregate function MIN – it does not return the name of the products with the minumum price, it returns the name of the first product that matches the minimum price.

SELECT itemID, description, MIN(price)
FROM item

	ItemID	Description	MIN(price)
1	868	Screen Wipes	2.0

Calculations and Other Functions

Aggregating functions can use other calculations:

SELECT MAX(price*quantity) 
FROM OrderItem, Item 
WHERE OrderItem.ItemID = Item.ItemID

	*MAX(pricequantity)**
1	88.0

Other functions can be used to modify the results of aggregate functions:

SELECT ROUND(AVG(price),2) AS "Average Item Price"
FROM item;

	Average Item Price
1	13.7

GROUP BY

Aggregate functions return single rows of values, as in the examples above.

Groups are used with aggregate functions to get results for each group. The SELECT clause should include a non-aggregate expression (the name of the group) to label each group:

Example: Find the average price for each item category:

SELECT category, ROUND(AVG(price),2) AS "Average Price"
FROM item
GROUP BY category;

	Category	Average Price
1	Boys Toys	14.1
2	Computer Accessories	13.0
3	Girls Gizmos	11.29
4	Office Distractions	10.89
5	Personal Grooming	18.0

The GROUP BY clause must be after any WHERE clause, and before any ORDER BY clause:

SELECT category, ROUND(AVG(price),2) AS "Average Price"
FROM item
WHERE category NOT LIKE "%toys%"
GROUP BY category
ORDER BY ROUND(AVG(price),2) DESC

	Category	Average Price
1	Personal Grooming	18.0
2	Computer Accessories	13.0
3	Girls Gizmos	11.29
4	Office Distractions	10.89

Grouping can be applied to single fields, or multiple fields.

Example: find total sales for each city/region and category for orders that have not been dispatched:

SELECT Address3 as "City/Region", Category, SUM(price*quantity) AS "Sales Pending"
FROM Customer c, CustOrder co, OrderItem oi, Item i
WHERE c.CustomerID = co.CustomerID
AND co.OrderNo = oi.OrderNo
AND oi.ItemID = i.ItemID
AND orderDispatched = ""
GROUP BY Address3, Category

	City/Region	Category	Sales Pending
1	Aberdeen	Boys Toys	4.0
2	Aberdeen	Computer Accessories	78.0
3	Aberdeen	Girls Gizmos	12.0
4	Aberdeen	Personal Grooming	30.0
5	Edinburgh	Boys Toys	14.0
6	Edinburgh	Computer Accessories	14.0
7	Glasgow	Computer Accessories	22.0
8	Glasgow	Girls Gizmos	32.0
9	Glasgow	Personal Grooming	34.0