Example:
SELECT city, COUNT(*) number,
FIXED (AVG(account),7,2) avg_account,
SUM(account) sum_account
FROM customer
GROUP BY city
HAVING COUNT(*) > 1
Compare the requesting command with the previous example. The line 'HAVING COUNT(*) > 1' eliminates all cities with only one customer.
The question could be asked, when HAVING is used instead of WHERE. Actually, their usage is much alike. A condition with WHERE excludes data from the table to be searched through, whereas HAVING is normally used after a function that is applied to a whole column.
Columns in the output list for which none of these functions has been issued must be grouped using GROUP BY. If a condition after HAVING is not satisfied, groups of values are excluded from the output. If one of the functions described above is applied to all columns of the output list, GROUP BY is not needed, and the condition is only checked for the only output row.
(Compare the following examples with those of Section Grouping Values: GROUP BY)
Show the minimum, the average, and the maximum account of all customers with an average account >= 0.00 for all cities, except New York.
SELECT city, MIN(account) min_account,
FIXED (AVG(account),7,2) avg_account,
MAX(account) max_account
FROM customer
WHERE city <> 'New York'
GROUP BY city
HAVING AVG(account) >= 0.00
For each city with more than two customers, show the number of customers living there and the average account:
SELECT city, COUNT(*) number, FIXED (AVG(account),7,2) avg_account
FROM customer
GROUP BY city
HAVING COUNT(*) > 2
Show the summed up account for each city with a total account of -100.00 at the most and two customers at least:
SELECT city, SUM(account) sum_account
FROM customer
GROUP BY city
HAVING COUNT(*) >= 2
AND SUM(account) <= -100.00
