the red penguin

13. Grouping data in SQL

13.01 Grouping

Let’s say we’re going shopping, not just for ourselves, but for our mum and neighbour too.

We can identify who the products are bought for, and group them together:

Then we can add up the price of each group like this:

Let’s say this is all in a table called Shopping. We could do a query to find the items bought for Mum:

SELECT * FROM Shopping WHERE BoughtFor = "Mum";

There is a construct called GROUP BY which can enable us to group items. For example we can get the name and the sum of the Price fields and group it by BoughtFor name, like this:

SELECT BoughtFor, SUM(Price) FROM Shopping GROUP BY BoughtFor;

13.02 Aggregate functions

Along with SUM, there are other aggregate functions:

SUM – Computes a regular sum of the group
AVG – Computes the average of the group
STD – Computes the standard deviation of the group
VARIANCE – Computes the variance of the group
MAX – Produces the maximum values of the group
MIN – Produces the minimum value of the group – MAX and MIN are useful for dates
COUNT – Produces a count of the number of things we have aggregated in our group
COUNT(DISTINCT) – Produces a count of the distinct items in the group, so removes duplicates from the list
GROUP_CONCAT – Valid for string data, concatenates the entire group into a single string

Tuesday 23 November 2021, 416 views

Leave a Reply

Your email address will not be published. Required fields are marked *