SQL GROUP BY and HAVING

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

GROUP BY...

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 columnName, SUM(columnName) FROM tableName GROUP BY columnName;

GROUP BY Example

This "Books" table:

Title	Author	Publisher	Year	Price
الدورة الدموية	إبن النفيس	دار العلم	1650	230.50
Java 2	L. Johnston	Fast Press	2002	120.99
Linux and Unix	J. Sam	Fast Press	2004	210.99
Operating Systems	M. Stone	Coriolis	2005	167.00
Web Programming	K. Yariv	East Edition	2005	140.60
XML Language	M. Salim	Knowledge Press	2000	90.45

And this SQL:

SELECT Publisher, Price FROM Books;

Returns this result:

Publisher	Price
دار العلم	230.50
Fast Press	120.99
Fast Press	210.99
Coriolis	167.00
East Edition	140.60
Knowledge Press	90.45

In order to get the sum of prices of the books published by each publisher, we use the following SQL:

SELECT Publisher, SUM(Price) FROM Books GROUP BY Publisher;

The result is:

Publisher	SUM(Price)
دار العلم	230.50
Coriolis	167.00
East Edition	140.60
Fast Press	331.98
Knowledge Press	90.45

Now the "Fast Press" appears only once.

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 columnName, SUM(columnName) FROM tableName GROUP BY columnName HAVING SUM(columnName) condition value;

The "Books" table and this SQL:

SELECT Publisher,SUM(Price) FROM Books GROUP BY Publisher HAVING SUM(Price) >= 300.00;

Returns this result

Publisher	SUM(Price)
Fast Press	331.98

Test your SQL Skills

To test your SQL skills click here.