← Back to branch summary

~drizzle-trunk/drizzle/development

« back to all changes in this revision

Viewing changes to docs/groupby.rst

  • Committer: Lee Bieber
  • Date: 2010-11-14 23:15:42 UTC
  • mfrom: (1929.1.42 warning-stack-frame)
  • Revision ID: kalebral@gmail.com-20101114231542-fnnu6ydd2p17n582
Merge Monty - fix bug 672372: some functions use > 32k stack

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
 
        SELECT column_name, aggregate_function(column_name)
9
 
        FROM table_name
10
 
        WHERE column_name operator value
11
 
        GROUP BY column_name
12
 
 
13
 
**GROUP BY Clause Example**
14
 
 
15
 
The "Activities" table:
16
 
 
17
 
+---------+--------------+--------------+-------------+----------+
18
 
|Id       |ActivityDate  |ActivityType  |ActivityCost | userID   |
19
 
+=========+==============+==============+=============+==========+
20
 
| 1       |2011-01-02    | Sport        |45           |131       |
21
 
+---------+--------------+--------------+-------------+----------+
22
 
| 2       |2011-01-02    | Art          |10           |256       |
23
 
+---------+--------------+--------------+-------------+----------+
24
 
| 3       |2011-01-02    | Music        |25           |022       |
25
 
+---------+--------------+--------------+-------------+----------+
26
 
| 4       |2011-01-02    | Food         |125          |022       |
27
 
+---------+--------------+--------------+-------------+----------+
28
 
| 5       |2011-01-03    | Music        |40           |131       |
29
 
+---------+--------------+--------------+-------------+----------+
30
 
| 6       |2011-01-03    | Food         |20           |175       |
31
 
+---------+--------------+--------------+-------------+----------+
32
 
 
33
 
Running the following simple query::
34
 
 
35
 
        SELECT userID
36
 
        FROM activities
37
 
        GROUP BY userID;
38
 
 
39
 
Returns:
40
 
 
41
 
+---------+
42
 
| userID  |
43
 
+=========+
44
 
| 131     |
45
 
+---------+
46
 
| 256     |
47
 
+---------+
48
 
| 022     |
49
 
+---------+
50
 
| 175     |
51
 
+---------+
52
 
 
53
 
(This shows that GROUP BY accepts a column_name and consolidates like customer values.)
54
 
 
55
 
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.
56
 
 
57
 
You could use the following SQL statement: ::
58
 
 
59
 
        SELECT userID,SUM(ActivityCost) AS "Activity Total"
60
 
        FROM Activities
61
 
        GROUP BY userID;
62
 
 
63
 
The result-set will look like this:
64
 
 
65
 
======    ==============
66
 
userID    Activity Total
67
 
======    ==============
68
 
131       85
69
 
256       10
70
 
022       150
71
 
175       20
72
 
======    ==============
73
 
 
74
 
With the aggregate SUM() function, SQL can calculate how much each unique user has spent on activities over time.
75
 
 
76
 
We can also use the GROUP BY statement on more than one column, like this: ::
77
 
 
78
 
        SELECT userID,ActivityDate,SUM(ActivityCost)
79
 
        FROM Activities
80
 
        GROUP BY userID,ActivityDate;
81