4
The GROUP BY clause is used to extract only those records that fulfill a specified criterion.
10
SELECT column_name, aggregate_function(column_name)
12
WHERE column_name operator value
15
**GROUP BY Clause Example**
17
The "Activities" table:
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
+---------+--------------+--------------+-------------+----------+
35
Running the following simple query
57
(This shows that GROUP BY accepts a column_name and consolidates like customer values.)
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.
61
You could use the following SQL statement:
65
SELECT userID,SUM(ActivityCost) AS "Activity Total"
69
The result-set will look like this:
80
With the aggregate SUM() function, SQL can calculate how much each unique user has spent on activities over time.
82
We can also use the GROUP BY statement on more than one column, like this:
86
SELECT userID,ActivityDate,SUM(ActivityCost)
88
GROUP BY userID,ActivityDate;