SQL HAVING CLAUSE

The SQL HAVING clause is used to restrict conditionally the output of a SQL statement, by a SQL aggregate function used in your SELECT list of columns.

You can't specify criteria in a SQL WHERE clause against a column in the SELECT list for which SQL aggregate function is used. For example the following SQL statement will generate an error: 

SELECT Employee, SUM (Hours) FROM EmployeeHours WHERE SUM (Hours) > 24 GROUP BY Employee

The SQL HAVING clause is used to do exactly this, to specify a condition for an aggregate function which is used in your query: 

SELECT Employee, SUM (Hours) FROM EmployeeHours GROUP BY Employee HAVING SUM (Hours) > 24

The above SQL statement will select all employees and the sum of their respective hours, as long as this sum 

is greater than 24. The result of the SQL HAVING clause can be seen below: 

Employee	Hours
Scott Armstrong	25
Tina Crown	27

MORE BASIC SQL COMMANDS

SQL GROUP BY CLAUSE

The SQL GROUP BY statement is used along with the SQL aggregate functions like SUM to provide means of grouping the result dataset by certain database table column(s).  The best way to explain how and when to use the SQL GROUP BY statement is by example, and that’s what we are going to do.  Consider the following database table called EmployeeHours storing the daily hours for each employee of a factious company:  Employee Date Hours Scott Armstrong 5/6/2004 8 Allan Babel 5/6/2004 8 Tina Crown 5/6/2004 8 Scott Armstrong 5/7/2004 9 Allan Babel 5/7/2004 8 Tina Crown 5/7/2004 10 Scott Armstrong 5/8/2004 8 Allan Babel 5/8/2004 8 Tina Crown 5/8/2004 9 If the manager of the company wants to get the simple sum of all hours worked by all employees, he needs to execute the following SQL statement:  SELECT SUM (Hours) FROM EmployeeHours But what if the manager wants to get the sum of all hours for each of his employees? To do that he need to modify his SQL query and use the SQL GROUP BY statement:  SELECT Employee, SUM (Hours) FROM EmployeeHours GROUP BY Employee The result of the SQL expression above will be the following:  Employee Hours Scott Armstrong 25 Allan Babel 24 Tina Crown 27 As you can see we have only one entry for each employee, because we are grouping by the Employee column. The SQL GROUP BY clause can be used with other SQL aggregate functions, for example SQL AVG:  SELECT Employee, AVG(Hours) FROM EmployeeHours GROUP BY Employee  The result of the SQL statement above will be:  Employee Hours Scott Armstrong 8.33 Allan Babel 8 Tina Crown 9 In our Employee table we can group by the date column too, to find out what is the total number of hours worked on each of the dates into the table:  SELECT Date, SUM(Hours) FROM EmployeeHours GROUP BY Date Here is the result of the above SQL expression:  Date Hours 5/6/2004 24 5/7/2004 27 5/8/2004 25

MORE BASIC SQL COMMANDS

SQL SUM FUNCTION

The SQL SUM aggregate function allows selecting the total for a numeric column.

The SQL SUM syntax is displayed below: 

SELECT SUM(Column1) FROM Table1

We are going to use the Sales table to illustrate the use of SQL SUM clause:

Sales:

CustomerID	Date	SaleAmount
2	5/6/2004	$100.22
1	5/7/2004	$99.95
3	5/7/2004	$122.95
3	5/13/2004	$100.00
4	5/22/2004	$555.55

Consider the following SQL SUM statement: 

SELECT SUM(SaleAmount) FROM Sales

This SQL statement will return the sum of all SaleAmount fields and the result of it will be: 

SaleAmount

$978.67

Of course you can specify search criteria using the SQL WHERE clause in your SQL SUM statement. If you want to select the total sales for customer with CustomerID = 3, you will use the following SQL SUM statement: 

SELECT SUM(SaleAmount) FROM Sales WHERE CustomerID = 3

The result will be: 

SaleAmount

$222.95

MORE BASIC SQL COMMANDS