The “group by” statement groups lines in a table. Lines within a group have the same value in one or more columns.
One purpose of grouping is to display averages, sums, maximum values for each group. Example: show the total amount of sales for each state.
Note that in the result of such a query, we see only one line per group.
Display averages, sums, counts, maximum values within a group:
In the following example, we will show:
- the sum of revenues earned per state.
- the number of sales transactions per state.
- the amount of the biggest transaction per state.
- the amount of the smallest transaction per state.
- the average sales transaction amount per state.
To do this, we group the rows in the sales table by state:
How can I take into account only sales from 2014 in the results?
To do that we use a where statement as for a normal select. The syntax is:
How can I show only the regions for which the total revenue is over 50000?
In that case you need to use the keyword “having” instead of “where”. This is because you need to use the total revenue within the group (region) in the condition.