|
1994.4.16
by Marisa Plumb
new function and clause files, some placeholders for right now |
1 |
Having
|
|
1994.4.17
by Marisa Plumb
having and distinct clauses |
2 |
======
|
3 |
||
4 |
The WHERE keyword cannot be used with aggregate functions, but the HAVING clause can be; this is its primary use. |
|
5 |
||
6 |
SQL HAVING Syntax: ::
|
|
7 |
||
8 |
SELECT column_name, aggregate_function(column_name)
|
|
9 |
FROM table_name
|
|
10 |
WHERE column_name operator value
|
|
11 |
GROUP BY column_name
|
|
12 |
HAVING aggregate_function(column_name) operator value
|
|
13 |
||
14 |
**SQL HAVING Example**
|
|
15 |
||
16 |
Take a look at the "Activities" table: |
|
|
1994.5.24
by Stewart Smith
\r and trailing whitespace removed. |
17 |
|
18 |
+---------+--------------+--------------+-------------+----------+ |
|
19 |
|Id |ActivityDate |ActivityType |ActivityCost | userID | |
|
20 |
+=========+==============+==============+=============+==========+ |
|
21 |
| 1 |2011-01-02 | Sport |45 |131 |
|
|
22 |
+---------+--------------+--------------+-------------+----------+ |
|
23 |
| 2 |2011-01-02 | Art |10 |256 |
|
|
24 |
+---------+--------------+--------------+-------------+----------+ |
|
25 |
| 3 |2011-01-02 | Music |25 |022 |
|
|
26 |
+---------+--------------+--------------+-------------+----------+ |
|
27 |
| 4 |2011-01-02 | Food |125 |022 |
|
|
28 |
+---------+--------------+--------------+-------------+----------+ |
|
29 |
| 5 |2011-01-03 | Music |40 |131 |
|
|
30 |
+---------+--------------+--------------+-------------+----------+ |
|
31 |
| 6 |2011-01-03 | Food |20 |175 |
|
|
|
1994.4.17
by Marisa Plumb
having and distinct clauses |
32 |
+---------+--------------+--------------+-------------+----------+ |
33 |
||
34 |
In order to find if any users have spent more than $100 on recreational activities, use the following SQL statement: ::
|
|
35 |
||
36 |
SELECT userID,SUM(ActivityCost) FROM Activities
|
|
37 |
GROUP BY userID
|
|
38 |
HAVING SUM(ActivityCost)>100;
|
|
39 |
||
40 |
The result-set will look like this: |
|
41 |
||
|
1994.5.24
by Stewart Smith
\r and trailing whitespace removed. |
42 |
====== =============== |
43 |
userID SUM(OrderPrice) |
|
44 |
====== =============== |
|
45 |
022 150 |
|
|
1994.4.17
by Marisa Plumb
having and distinct clauses |
46 |
====== =============== |
47 |
||
48 |
Now we want to find if userIDs "131", "256", or "175" spent less than $50 on Activities. |
|
49 |
||
50 |
We add an ordinary WHERE clause to the SQL statement: ::
|
|
51 |
||
52 |
SELECT userID,SUM(ActivityCost) FROM Activities
|
|
53 |
WHERE userID='131' OR userID='256' OR userID="175"
|
|
54 |
GROUP BY userID
|
|
55 |
HAVING SUM(ActivityCost)<50;
|
|
56 |
||
57 |
The result-set would be: |
|
58 |
||
|
1994.5.24
by Stewart Smith
\r and trailing whitespace removed. |
59 |
====== =============== |
60 |
userID SUM(OrderPrice) |
|
61 |
====== =============== |
|
|
1994.4.17
by Marisa Plumb
having and distinct clauses |
62 |
256 10 |
|
1994.5.24
by Stewart Smith
\r and trailing whitespace removed. |
63 |
175 20 |
|
1994.4.17
by Marisa Plumb
having and distinct clauses |
64 |
====== =============== |