SQL aggregate functions and grouping

SQL aggregate functions help you summarize the data in a table. For example, you can count products, find their average price or add up the prices of products in a set.

COUNT counts rows, SUM adds values, AVG calculates the average, and MIN and MAX find the minimum and maximum.

Our examples use the candy table. The price field contains the price, and filling indicates whether the candy has a filling.

• COUNT
• SUM, AVG, MIN, MAX
• GROUP BY: group rows
• HAVING: filter groups by a condition

The candy table

With the “With filling” filter on, the catalogue shows three items. Check both the number of items and how many have a price.

AS gives a result column a name. Here, candies is the number of candies and prices is the number of recorded prices.

SELECT COUNT(*) AS candies,
       COUNT(price) AS prices
FROM candy
WHERE filling = true;

All three filled candies have a price. Removing WHERE makes the count include all seven items in the catalogue.

Calculate the total, average, lowest and highest prices of candies with a filling.

SELECT SUM(price) AS total,
       AVG(price) AS mean_price,
       MIN(price) AS lowest,
       MAX(price) AS highest
FROM candy
WHERE filling = true;

The three prices are 30, 60 and 90. That gives a total of 180 and an average of 180 / 3 = 60 per candy. The lowest and highest prices are 30 and 90.

The report shows two totals, one for filled candies and one for candies without a filling. Group the rows by filling to check both in one query.

GROUP BY collects rows with the same field value. SUM calculates a separate total for each group. The filling column in the result identifies the group.

In this example, filling appears twice. In GROUP BY it groups the candies by whether they have a filling. In SELECT it shows which group each total belongs to.

SELECT filling, SUM(price) AS total
FROM candy
GROUP BY filling;

The three filled varieties total 180 and the four without a filling total 170. The two report rows should add up to the total for the whole catalogue.

Look for candies that share both a price and a filling status. Return only the combinations that occur more than once.

List both fields in GROUP BY, separated by a comma. Each group now represents one combination of filling and price. COUNT(*) gives the number of rows in that group.

HAVING filters groups by a condition. In this example, keep only groups containing two or more candies.

SELECT filling, price, COUNT(*) AS qty
FROM candy
GROUP BY filling, price
HAVING COUNT(*) > 1;

Pluto's Tears and Mint Vortex both cost 40 and neither has a filling. Every other combination has only one row.