SQL GROUP BY

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:

select state,
sum(revenue),
count(*),
max(revenue),
min(revenue),
avg(revenue)
from sales
group 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:

select state, sum(revenue)
from sales
where year=2014
group by state

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.

Syntax:

select state, sum(revenue)
from sales
group by state
having sum(revenue)>50000

 

To test your understanding of this lesson, take the SQL Group By Quiz or practice group by using a real database, practice group by part II, practice group by part III. You can also go to the next chapter about modifying data with SQL
 
Share on:

Something not clear? Ask your question here:

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>

 
 

Author:
Last udpated: September 10, 2017
Created: August 14, 2014