~drizzle-trunk/drizzle/development

1994.5.25 by Stewart Smith
\r and trailing whitespace removed.
1
Group By
2
========
3
4
The GROUP BY clause is used to extract only those records that fulfill a specified criterion.
5
2165.2.5 by Andrew Hutchings
Make fixes to temporal docs
6
SQL GROUP BY Syntax
7
8
.. code-block:: mysql
1994.5.25 by Stewart Smith
\r and trailing whitespace removed.
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
15
**GROUP BY Clause Example**
16
17
The "Activities" table:
18
19
+---------+--------------+--------------+-------------+----------+
20
|Id       |ActivityDate  |ActivityType  |ActivityCost | userID   |
21
+=========+==============+==============+=============+==========+
22
| 1       |2011-01-02    | Sport        |45           |131       |
23
+---------+--------------+--------------+-------------+----------+
24
| 2       |2011-01-02    | Art          |10           |256       |
25
+---------+--------------+--------------+-------------+----------+
26
| 3       |2011-01-02    | Music        |25           |022       |
27
+---------+--------------+--------------+-------------+----------+
28
| 4       |2011-01-02    | Food         |125          |022       |
29
+---------+--------------+--------------+-------------+----------+
30
| 5       |2011-01-03    | Music        |40           |131       |
31
+---------+--------------+--------------+-------------+----------+
32
| 6       |2011-01-03    | Food         |20           |175       |
1994.4.12 by Marisa Plumb
markup fixes
33
+---------+--------------+--------------+-------------+----------+
34
2165.2.5 by Andrew Hutchings
Make fixes to temporal docs
35
Running the following simple query
36
37
.. code-block:: mysql
1994.4.12 by Marisa Plumb
markup fixes
38
39
	SELECT userID
40
	FROM activities
1994.5.25 by Stewart Smith
\r and trailing whitespace removed.
41
	GROUP BY userID;
42
1994.4.12 by Marisa Plumb
markup fixes
43
Returns:
44
1994.5.25 by Stewart Smith
\r and trailing whitespace removed.
45
+---------+
46
| userID  |
47
+=========+
48
| 131     |
49
+---------+
50
| 256     |
51
+---------+
52
| 022     |
53
+---------+
54
| 175     |
55
+---------+
56
57
(This shows that GROUP BY accepts a column_name and consolidates like customer values.)
58
59
However, GROUP BY is much more powerful when used with an aggregate function. Let's say you want to find the total amount spent by each unique User.
60
2165.2.5 by Andrew Hutchings
Make fixes to temporal docs
61
You could use the following SQL statement:
62
63
.. code-block:: mysql
1994.5.25 by Stewart Smith
\r and trailing whitespace removed.
64
1994.4.12 by Marisa Plumb
markup fixes
65
	SELECT userID,SUM(ActivityCost) AS "Activity Total"
66
	FROM Activities
1994.5.25 by Stewart Smith
\r and trailing whitespace removed.
67
	GROUP BY userID;
68
1994.4.12 by Marisa Plumb
markup fixes
69
The result-set will look like this:
1994.5.25 by Stewart Smith
\r and trailing whitespace removed.
70
71
======    ==============
72
userID    Activity Total
73
======    ==============
74
131       85
75
256       10
76
022       150
77
175       20
78
======    ==============
79
80
With the aggregate SUM() function, SQL can calculate how much each unique user has spent on activities over time.
81
2165.2.5 by Andrew Hutchings
Make fixes to temporal docs
82
We can also use the GROUP BY statement on more than one column, like this:
83
84
.. code-block:: mysql
1994.5.25 by Stewart Smith
\r and trailing whitespace removed.
85
86
	SELECT userID,ActivityDate,SUM(ActivityCost)
87
	FROM Activities
1994.4.12 by Marisa Plumb
markup fixes
88
	GROUP BY userID,ActivityDate;
89