← Back to branch summary

~drizzle-trunk/drizzle/development

« back to all changes in this revision

Viewing changes to docs/having.rst

  • Committer: Lee Bieber
  • Date: 2011-01-05 05:15:02 UTC
  • mfrom: (2055.1.2 build)
  • Revision ID: kalebral@gmail.com-20110105051502-9v4xuoozzpkka8rs
Merge Evan - fix bug 682773 libdrizzle performance: in non-blocking mode don't attempt to read after write
Merge Stewart - add in more tests from the suites directory

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
 
        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
 
======    ===============