**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

Previous post: 12. SQL refresher

Databases and Advanced Data Techniques index

- 14. Connecting to SQL in other JS and PHP
- 13. Grouping data in SQL
- 12. SQL refresher
- 11. Malice and accidental damage
- 10. ACID: Guaranteeing a DBMS against errors
- 9. Normalization example
- 8. Database normalization
- 7. Data integrity and security
- 6. Database integrity
- 5. Joins in SQL
- 4. Introduction to SQL
- 3. Relational Databases
- 2. What shape is your data?
- 1. Sources of data

© The Red Penguin 2000-2021. Follow us on Twitter.

## Leave a Reply