← Back to branch summary

~drizzle-trunk/drizzle/development

« back to all changes in this revision

Viewing changes to docs/having.rst

  • Committer: Brian Aker
  • Date: 2011-01-12 06:45:23 UTC
  • mto: (2073.1.4 catalogs)
  • mto: This revision was merged to the branch mainline in revision 2080.
  • Revision ID: brian@tangent.org-20110112064523-rqhptaqbph22qmj1
Remove custom error.

Show diffs side-by-side

added added

removed removed

Lines of Context:
docs/having.rst
3
3
 
4
4
The WHERE keyword cannot be used with aggregate functions, but the HAVING clause can be; this is its primary use.
5
5
 
6
 
SQL HAVING Syntax:
7
 
 
8
 
.. code-block:: mysql
 
6
SQL HAVING Syntax: ::
9
7
 
10
8
        SELECT column_name, aggregate_function(column_name)
11
9
        FROM table_name
16
14
**SQL HAVING Example**
17
15
 
18
16
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
 
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       |
 
32
+---------+--------------+--------------+-------------+----------+
 
33
 
 
34
In order to find if any users have spent more than $100 on recreational activities, use the following SQL statement: ::
39
35
 
40
36
        SELECT userID,SUM(ActivityCost) FROM Activities
41
37
        GROUP BY userID
43
39
 
44
40
The result-set will look like this:
45
41
 
46
 
======    ===============
47
 
userID    SUM(OrderPrice)
48
 
======    ===============
49
 
022       150
 
42
======    ===============
 
43
userID    SUM(OrderPrice)
 
44
======    ===============            
 
45
022       150                  
50
46
======    ===============
51
47
 
52
48
Now we want to find if userIDs "131", "256", or "175" spent less than $50 on Activities.
53
49
 
54
 
We add an ordinary WHERE clause to the SQL statement:
55
 
 
56
 
.. code-block:: mysql
 
50
We add an ordinary WHERE clause to the SQL statement: ::
57
51
 
58
52
        SELECT userID,SUM(ActivityCost) FROM Activities
59
53
        WHERE userID='131' OR userID='256' OR userID="175"
62
56
 
63
57
The result-set would be:
64
58
 
65
 
======    ===============
66
 
userID    SUM(OrderPrice)
67
 
======    ===============
 
59
======    ===============
 
60
userID    SUM(OrderPrice)
 
61
======    ===============            
68
62
256       10
69
 
175       20
 
63
175       20                  
70
64
======    ===============