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