Aggregate functions (like SUM) often need an added GROUP BY functionality.
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 |
This "Sales" Table:
| Company | Amount |
|---|---|
| W3Schools | 5500 |
| IBM | 4500 |
| W3Schools | 7100 |
And This SQL:
SELECT Company, SUM(Amount) FROM Sales |
Returns this result:
| Company | SUM(Amount) |
|---|---|
| W3Schools | 17100 |
| IBM | 17100 |
| W3Schools | 17100 |
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:
| Company | SUM(Amount) |
|---|---|
| W3Schools | 12600 |
| IBM | 4500 |
The HAVING keyword has been added to SQL because a WHERE keyword can not 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:
| Company | Amount |
|---|---|
| W3Schools | 5500 |
| IBM | 4500 |
| W3Schools | 7100 |
This SQL:
SELECT Company,SUM(Amount) FROM Sales GROUP BY Company HAVING SUM(Amount)>10000 |
Returns this result
| Company | SUM(Amount) |
|---|---|
| W3Schools | 12600 |