~drizzle-trunk/drizzle/development

1994.4.16 by Marisa Plumb
new function and clause files, some placeholders for right now
1
Having
1994.4.17 by Marisa Plumb
having and distinct clauses
2
======
3
4
The WHERE keyword cannot be used with aggregate functions, but the HAVING clause can be; this is its primary use.
5
2165.2.5 by Andrew Hutchings
Make fixes to temporal docs
6
SQL HAVING Syntax:
7
8
.. code-block:: mysql
1994.4.17 by Marisa Plumb
having and distinct clauses
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:
1994.5.24 by Stewart Smith
\r and trailing whitespace removed.
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       |
1994.4.17 by Marisa Plumb
having and distinct clauses
34
+---------+--------------+--------------+-------------+----------+
35
2165.2.5 by Andrew Hutchings
Make fixes to temporal docs
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
1994.4.17 by Marisa Plumb
having and distinct clauses
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
1994.5.24 by Stewart Smith
\r and trailing whitespace removed.
46
======    ===============
47
userID    SUM(OrderPrice)
48
======    ===============
49
022       150
1994.4.17 by Marisa Plumb
having and distinct clauses
50
======    ===============
51
52
Now we want to find if userIDs "131", "256", or "175" spent less than $50 on Activities.
53
2165.2.5 by Andrew Hutchings
Make fixes to temporal docs
54
We add an ordinary WHERE clause to the SQL statement:
55
56
.. code-block:: mysql
1994.4.17 by Marisa Plumb
having and distinct clauses
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
1994.5.24 by Stewart Smith
\r and trailing whitespace removed.
65
======    ===============
66
userID    SUM(OrderPrice)
67
======    ===============
1994.4.17 by Marisa Plumb
having and distinct clauses
68
256       10
1994.5.24 by Stewart Smith
\r and trailing whitespace removed.
69
175       20
1994.4.17 by Marisa Plumb
having and distinct clauses
70
======    ===============