11
11
GROUP BY column_name
14
**GROUP BY Clause Example**
16
16
The "Activities" table:
18
+---------+--------------+-------------+----------+
19
|Id |ActivityDate |ActivityType |User |
20
+=========+==============+=============+==========+
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
+---------+--------------+-------------+----------+
34
If you want to select only the persons living in the city "Chicago" from the table above, use the following SELECT statement: ::
39
The result-set will look like this:
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
+---------+--------------+--------------+-------------+----------+
34
Running the following simple query::
54
(This shows that GROUP BY accepts a column_name and consolidates like customer values.)
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.
58
You could use the following SQL statement: ::
60
SELECT userID,SUM(ActivityCost) AS "Activity Total"
64
The result-set will look like this:
75
With the aggregate SUM() function, SQL can calculate how much each unique user has spent on activities over time.
77
We can also use the GROUP BY statement on more than one column, like this: ::
79
SELECT userID,ActivityDate,SUM(ActivityCost)
81
GROUP BY userID,ActivityDate;