← Back to branch summary

~drizzle-trunk/drizzle/development

« back to all changes in this revision

Viewing changes to docs/groupby.rst

markup fixes

Show diffs side-by-side

added added

removed removed

Lines of Context:
docs/groupby.rst
11
11
        GROUP BY column_name
12
12
 
13
13
        
14
 
WHERE Clause Example
 
14
**GROUP BY Clause Example**
15
15
 
16
16
The "Activities" table:
17
17
 
18
 
+---------+--------------+-------------+----------+
19
 
|Id       |ActivityDate  |ActivityType |User      |
20
 
+=========+==============+=============+==========+
21
 
| 1       |              | Sue         |Larson    | 
22
 
+---------+--------------+-------------+----------+
23
 
| 2       | Roberts      | Teri        |Roberts   |
24
 
+---------+--------------+-------------+----------+
25
 
| 3       | Peterson     | Kari        |Peterson  | 
26
 
+---------+--------------+-------------+----------+
27
 
| 4       | Larson       | Sue         |Smith     | 
28
 
+---------+--------------+-------------+----------+
29
 
| 5       | Roberts      | Teri        |Dagwood   |
30
 
+---------+--------------+-------------+----------+
31
 
| 6       | Peterson     | Kari        |Masters   | 
32
 
+---------+--------------+-------------+----------+
33
 
 
34
 
If you want to select only the persons living in the city "Chicago" from the table above, use the following SELECT statement: ::
35
 
 
36
 
        SELECT * FROM Persons
37
 
        WHERE City='Chicago'
38
 
 
39
 
The result-set will look like this:
40
 
 
41
 
+---------+------------+----------+----------+--------+
42
 
| Id      |LastName    |FirstName |Address   |City    |
43
 
+---------+------------+----------+----------+--------+
44
 
|1        | Larson     | Sue      |3 Cherry  | Chicago|
45
 
+---------+------------+----------+----------+--------+
46
 
|2        | Roberts    | Teri     |21 Brown  | Chicago|
47
 
+---------+------------+----------+----------+--------+
 
 
b'\\ No newline at end of file'
 
18
+---------+--------------+--------------+-------------+----------+
 
19
|Id       |ActivityDate  |ActivityType  |ActivityCost | userID   |
 
20
+=========+==============+==============+=============+==========+
 
21
| 1       |2011-01-02    | Sue          |45           |131       |
 
22
+---------+--------------+--------------+-------------+----------+
 
23
| 2       |2011-01-02    | Teri         |10           |256       |
 
24
+---------+--------------+--------------+-------------+----------+
 
25
| 3       |2011-01-02    | Kari         |25           |022       |
 
26
+---------+--------------+--------------+-------------+----------+
 
27
| 4       |2011-01-02    | Sue          |125          |022       |
 
28
+---------+--------------+--------------+-------------+----------+
 
29
| 5       |2011-01-03    | Teri         |40           |131       |
 
30
+---------+--------------+--------------+-------------+----------+
 
31
| 6       |2011-01-03    | Kari         |20           |175       |
 
32
+---------+--------------+--------------+-------------+----------+
 
33
 
 
34
Running the following simple query::
 
35
 
 
36
        SELECT userID
 
37
        FROM activities
 
38
        GROUP BY userID;
 
39
 
 
40
Returns:
 
41
 
 
42
+---------+
 
43
| userID  |
 
44
+=========+
 
45
| 131     |
 
46
+---------+
 
47
| 256     |
 
48
+---------+
 
49
| 022     |
 
50
+---------+
 
51
| 175     |
 
52
+---------+
 
53
 
 
54
(This shows that GROUP BY accepts a column_name and consolidates like customer values.)
 
55
 
 
56
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.
 
57
 
 
58
You could use the following SQL statement: ::
 
59
        
 
60
        SELECT userID,SUM(ActivityCost) AS "Activity Total"
 
61
        FROM Activities
 
62
        GROUP BY userID;
 
63
 
 
64
The result-set will look like this:
 
65
 
 
66
======    ==============
 
67
userID    Activity Total
 
68
======    ==============
 
69
131       85             
 
70
256       10             
 
71
022       150            
 
72
175       20             
 
73
======    ==============
 
74
 
 
75
With the aggregate SUM() function, SQL can calculate how much each unique user has spent on activities over time.
 
76
 
 
77
We can also use the GROUP BY statement on more than one column, like this: ::
 
78
        
 
79
        SELECT userID,ActivityDate,SUM(ActivityCost) 
 
80
        FROM Activities
 
81
        GROUP BY userID,ActivityDate;
 
82