← Back to branch summary

~drizzle-trunk/drizzle/development

« back to all changes in this revision

Viewing changes to docs/groupby.rst

  • Committer: Brian Aker
  • Date: 2011-02-22 06:12:02 UTC
  • mfrom: (2190.1.6 drizzle-build)
  • Revision ID: brian@tangent.org-20110222061202-k03czxykqy4x9hjs
List update, header fixes, multiple symbols, and David deletes some code.

Show diffs side-by-side

added added

removed removed

Lines of Context:
docs/groupby.rst
 
1
Group By
 
2
========
 
3
 
 
4
The GROUP BY clause is used to extract only those records that fulfill a specified criterion.
 
5
 
 
6
SQL GROUP BY Syntax
 
7
 
 
8
.. code-block:: mysql
 
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       |
 
33
+---------+--------------+--------------+-------------+----------+
 
34
 
 
35
Running the following simple query
 
36
 
 
37
.. code-block:: mysql
 
38
 
 
39
        SELECT userID
 
40
        FROM activities
 
41
        GROUP BY userID;
 
42
 
 
43
Returns:
 
44
 
 
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
 
 
61
You could use the following SQL statement:
 
62
 
 
63
.. code-block:: mysql
 
64
 
 
65
        SELECT userID,SUM(ActivityCost) AS "Activity Total"
 
66
        FROM Activities
 
67
        GROUP BY userID;
 
68
 
 
69
The result-set will look like this:
 
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
 
 
82
We can also use the GROUP BY statement on more than one column, like this:
 
83
 
 
84
.. code-block:: mysql
 
85
 
 
86
        SELECT userID,ActivityDate,SUM(ActivityCost)
 
87
        FROM Activities
 
88
        GROUP BY userID,ActivityDate;
 
89