GROUP BY Statement
GROUP BY clause will help you break up results based on column values.
GROUP BY will let you use some of the functions against logical groupings of the information in your tables.
You can also use it to form logical breaks in the information in the results set.
For example, if you're working with a sales history table, you can use
GROUP BY to help in breaking the information up by state. You can then subtotal the sales values for a state:
SELECT * FROM Sales
GROUP BY State
The GROUP BY clause, which groups results according to the parameters set forth in the clause, finds out information about a particular record, whereas aggregation
summarizes more than one record.
This module examines
- the COUNT() function, which counts records;
- the SUM() function, which adds the value of records together;
- the AVG() function, which finds averages; and finally, the MAX() and MIN() functions, which find the lowest and highest values in a set of records.
This section examines the GROUP BY clause, which is used in conjunction with the SELECT statement.
It allows you to group identical data into one subset rather than listing each record.
The GROUP BY clause is at its most powerful when used with SQL's summarizing and aggregating functions, which are covered in the next section.
The aim of this module is to get a handle on how GROUP BY works and how to use it more effectively.
Begin by looking at how GROUP BY can answer the question,
Which states do members of the film club live in?
The answer does not require a list of every member and the state they live in; you simply want a list of the specific different states. Use the GROUP BY clause to answer this question, even though strictly speaking SELECT DISTINCT would work just as well:
GROUP BY State;
The GROUP BY clause must go after any FROM or WHERE clauses in the SELECT statement.
All the columns you want to be grouped must be listed in the GROUP BY column list.
For example, the preceding code groups by the State column. If you want to include more than one column in the GROUP BY clause,
then separate the columns with commas, in the same way that you would separate columns in a SELECT statement's column list.
The preceding SQL produces the results shown in the following table.
One of the values in the table is NULL, so you end up with one group that is NULL: