~drizzle-trunk/drizzle/development

« back to all changes in this revision

Viewing changes to docs/having.rst

  • Committer: Lee Bieber
  • Date: 2011-01-08 01:57:02 UTC
  • mfrom: (1994.4.21 drizzle)
  • mto: This revision was merged to the branch mainline in revision 2063.
  • Revision ID: kalebral@gmail.com-20110108015702-d9mc4jwppb9vej94
Merge Marisa - latest doc updates, fix sphinx warnings

Show diffs side-by-side

added added

removed removed

Lines of Context:
 
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
        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:
 
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: ::
 
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
 
 
42
======    ===============
 
43
userID    SUM(OrderPrice)
 
44
======    ===============            
 
45
022       150                  
 
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
 
 
59
======    ===============
 
60
userID    SUM(OrderPrice)
 
61
======    ===============            
 
62
256       10
 
63
175       20                  
 
64
======    ===============