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

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;

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

Calculations and Other Functions

Aggregating functions can use other calculations:

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

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

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

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;

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

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