← Back to branch summary

~drizzle-trunk/drizzle/development

« back to all changes in this revision

Viewing changes to docs/having.rst

  • Committer: Olaf van der Spek
  • Date: 2011-03-06 15:49:49 UTC
  • mto: (2226.1.1 build)
  • mto: This revision was merged to the branch mainline in revision 2227.
  • Revision ID: olafvdspek@gmail.com-20110306154949-zzso0l15mbwi60xb
Provide drizzle/drizzle.h

Show diffs side-by-side

added added

removed removed

Lines of Context:
docs/having.rst
 
1
Having
 
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
.. code-block:: mysql
 
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:
 
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       |
 
34
+---------+--------------+--------------+-------------+----------+
 
35
 
 
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
 
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
 
 
46
======    ===============
 
47
userID    SUM(OrderPrice)
 
48
======    ===============
 
49
022       150
 
50
======    ===============
 
51
 
 
52
Now we want to find if userIDs "131", "256", or "175" spent less than $50 on Activities.
 
53
 
 
54
We add an ordinary WHERE clause to the SQL statement:
 
55
 
 
56
.. code-block:: mysql
 
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
 
 
65
======    ===============
 
66
userID    SUM(OrderPrice)
 
67
======    ===============
 
68
256       10
 
69
175       20
 
70
======    ===============