SQL GROUP BY | SQL Tutorials

Home >> SQL GROUP BY

GROUP BY

PDF

Group By

Aggregate functions (like SUM) often need an added GROUP BY functionality.

GROUP BY was added to SQL because aggregate functions (like SUM) return the aggregate of all column values every time they are called and without the GROUP BY function it was impossible to find the sum for each individual group of column values.

The syntax for the GROUP BY function is:

SELECT column, SUM(column) FROM table GROUP BY column

GROUP BY Example

This "Sales" table:

Company Amount
Mesh Digital 5500
I'm a Celeb 4500
Mesh Digital 7100

And this SQL:

SELECT Company, SUM(Amount) FROM Sales

Returns this result:

Company SUM(Amount)
Mesh Digital 17100
I'm a Celeb 17100
Mesh Digital 17100

The above code is invalid because the column returned is not part of an aggregate. A GROUP BY clause will solve this problem:

SELECT Company, SUM(Amount) FROM Sales GROUP BY Company

Returns this result:

Company Amount
Mesh Digital 12600
I'm a Celeb 4500

HAVING

HAVING was added to SQL because the WHERE keyword could not be used against aggregate functions (like SUM) and without HAVING it would be impossible to test for result conditions.

The syntax for the HAVING function is:

SELECT column, SUM(column) FROM table GROUP BY column HAVING SUM(column) condition value

HAVING Example

This "Sales" table:

Company Amount
Mesh Digital 5500
I'm a Celeb 4500
Mesh Digital 7100

This SQL:

SELECT Company, SUM(Amount) FROM Sales GROUP BY Company HAVING SUM(Amount) > 10000

Returns this result:

Company Amount
Mesh Digital 12600

© Pixel Kicks, The Sharp Project, Thorpe Road, Manchester, M40 5BJ