"Fossies" - the Fresh Open Source Software Archive

Member "drizzle-7.1.36-stable/docs/groupby.rst" (6 May 2012, 2493 Bytes) of package /linux/misc/old/drizzle-7.1.36-stable.tar.gz:


As a special service "Fossies" has tried to format the requested source page into HTML format (assuming markdown format). Alternatively you can here view or download the uninterpreted source code file. A member file download can also be achieved by clicking within a package contents listing on the according byte size field.

GROUP BY

The GROUP BY clause is used to extract only those records that fulfill a specified criterion.

SQL GROUP BY Syntax

SELECT column_name, aggregate_function(column_name)
FROM table_name
WHERE column_name operator value
GROUP BY column_name

GROUP BY Clause Example

The "Activities" table:

Id ActivityDate ActivityType ActivityCost userID
1 2011-01-02 Sport 45 131
2 2011-01-02 Art 10 256
3 2011-01-02 Music 25 022
4 2011-01-02 Food 125 022
5 2011-01-03 Music 40 131
6 2011-01-03 Food 20 175

Running the following simple query

SELECT userID
FROM activities
GROUP BY userID;

Returns:

userID
131
256
022
175

(This shows that GROUP BY accepts a column_name and consolidates like customer values.)

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.

You could use the following SQL statement:

SELECT userID,SUM(ActivityCost) AS "Activity Total"
FROM Activities
GROUP BY userID;

The result-set will look like this:

userID Activity Total
131 85
256 10
022 150
175 20

With the aggregate SUM() function, SQL can calculate how much each unique user has spent on activities over time.

We can also use the GROUP BY statement on more than one column, like this:

SELECT userID,ActivityDate,SUM(ActivityCost)
FROM Activities
GROUP BY userID,ActivityDate;