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, 582 views
Next post: 14. Connecting to SQL in other JS and PHP Previous post: 12. SQL refresher
Databases and Advanced Data Techniques index
- 26. A very good guide to linked data
- 25. Information Retrieval
- 24. Triplestores and SPARQL
- 23. Ontologies – RDF Schema and OWL
- 22. RDF – Remote Description Framework
- 21. Linked Data – an introduction
- 20. Transforming XML databases
- 19. Semantic databases
- 18. Document databases and MongoDB
- 17. Key/Value databases and MapReduce
- 16. Distributed databases and alternative database models
- 15. Query efficiency and denormalisation
- 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
Leave a Reply