Please visit our sponsors !

SQL Group By and Having

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

The GROUP BY Keywords

The GROUP BY keywords have been added to SQL because aggregate functions (like SUM) return the aggregate of all column values every time they are called.

Without the GROUP BY functionality, finding the sum for each individual group of column values was not possible.

The syntax for the GROUP BY function is:

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

GROUP BY Example

This "Sales" Table:

And This SQL:

SELECT Company, SUM(Amount) FROM Sales

Returns this result:

The above code is invalid because the column returned is not part of an aggregate. A GROUP BY clause will correct, as in this SQL:

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

Returns this result:

The HAVING Keyword

The HAVING keyword has been added to SQL because a WHERE keyword cannot be used against aggregate functions (like SUM).

Without the HAVING keyword it would not be possible to test for function result conditions.

The syntax for the HAVING function is:

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

This "Sales" Table:

This SQL:

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

Returns this result

Jump to: Top of Page or HOME or Printer friendly page

Search W3Schools:

What Others Say About Us

Does the world know about us? Check out these places:

Dogpile Alta Vista MSN Google Excite Lycos Yahoo Ask Jeeves

We Help You For Free. You Can Help Us!

• Tell your newsgroup or mailing list
• Link to us from your pages
• Help us correct errors and broken links
• Help us with spelling and grammar
• Validate the XHTML code of this page

W3Schools is for training only. We do not warrant its correctness or its fitness for use. The risk of using it remains entirely with the user. While using this site, you agree to have read and accepted our terms of use and privacy policy.

Copyright 1999-2002 by Refsnes Data. All Rights Reserved

How we converted to XHTML