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