HAVING SQL

The HAVING clause is a way to select specific groups from a table. It is used in SELECT statements in conjunction with the GROUP BY clause. It operates in a manner very similar to the WHERE clause. The most common syntax looks like this:

SELECT column1,
SUM(column2)
FROM table
GROUP BY column1
HAVING SUM(column2) operator comparison
prop_idnamepricegroup
1Baltic Ave60purple
2Mediterranean Ave60purple
3Reading Railroad200rr
4Oriental Ave100light blue
5Vermont Ave100light blue
6Connecticut Ave120light blue

If we wished to look at our ubiquitous monopoly_prop table and find all of the color groups that have an aggregate value greater than 150 we could use this select SELECT to find them:

SELECT color,
SUM(price)
FROM monopoly_prop
GROUP BY color
HAVING SUM(price) > 150

The results of this query would be:

colorSUM(price)
light blue320
rr200

It should be noted that the GROUP BY clause can be used in conjunction with the WHERE clause. The where clause will first select the rows. The GROUP BY will group the rows SELECTed by that clause (not necessarily all of the rows in the table), then the HAVING clause will operate upon the groups (not the rows). The syntax:

SELECT column1, FUNCTION(column2)
FROM table
WHERE column2 meets some requirement
GROUP BY column1
HAVING FUNCTION(column2) some requirement

It looks long and drawn out, but perhaps we can illustrate by example. Now we wish to use the average function to find the average value of each color group only including the price of properties above 100.

SELECT color, AVG(price)
FROM monopoly_prop
WHERE price > 100
GROUP BY color
HAVING AVG(price) > 100

The result:

colorAVG(price)
light blue120
rr200

< GROUP BY SQL | INSERT INTO SQL >


Send notes in disappearing ink!

Interesting Pages