GROUP BY and FILTER

GROUP BY enables you to use aggregate functions on groups of data returned from a query.

FILTER is a modifier used on an aggregate function to limit the values used in an aggregation. All the columns in the select statement that aren’t aggregated should be specified in a GROUP BY clause in the query.

GROUP BY

Returning to a previous section, when we were working with aggregations, we used the aggregate function AVG to find out the average deal size. If we wanted to know the average value of the deals won by each sales person from highest average to lowest, the query would look like:

SELECT sales_agent,
       AVG(close_value)
  FROM sales_pipeline
 WHERE sales_pipeline.deal_stage = "Won"
 GROUP BY sales_agent
 ORDER BY AVG(close_value) DESC

We could even ascertain the average value of deals aggregated by manager by running a query with a join like this:

SELECT sales_teams.manager,
       AVG(sales_pipeline.close_value)
  FROM sales_teams
       JOIN sales_pipeline ON (sales_teams.sales_agent = sales_pipeline.sales_agent)
 WHERE sales_pipeline.deal_stage = "Won"
 GROUP BY sales_teams.manager

For example, let’s say you wanted to know the average deal by sales agent for each of their customers. If you used the query:

SELECT sales_agent,
       account,
       SUM(close_value)
  FROM sales_pipeline
 WHERE sales_pipeline.deal_stage = "Won"
 GROUP BY sales_agent
 ORDER BY sales_agent

you would get back the following table which shows each sales agent one time and chooses a value at random from the accounts won by that sales person:

|sales_agent|account|sum| |-|-| |Anna Snelling|Bioholding|275,056| |Boris Faz|Mathtouch|261,631| |Cassey Cress|Sumace|450,489| |Cecily Lampkin|Funholding|229,800| |Corliss Cosme|Zencorporation|421,036| |Daniell Hammack|Zathunicon|364,229| |Darcel Schlecht|Isdom|1,153,214| |Donn Cantrell|Bluth Company|445,860| |Elease Gluck|Dalttechnology|289,195| |Garret Kinder|Konex|197,773|

To get the average deal by sales agent for each account the query would look like this:

SELECT sales_agent,
       account,
       SUM(close_value)
  FROM sales_pipeline
 WHERE sales_pipeline.deal_stage = "Won"
 GROUP BY sales_agent, account
 ORDER BY sales_agent, account

The first several rows of the table returned would look like this:

FILTER

If you wanted to refine your query even more by running your aggregations against a limited set of the values in a column you could use the FILTER keyword. For example, if you wanted to know both the number of deals won by a sales agent and the number of those deals that had a value greater than 1000, you could use the query:

SELECT sales_agent,
       COUNT(sales_pipeline.close_value) AS total,
       COUNT(sales_pipeline.close_value)
FILTER(WHERE sales_pipeline.close_value > 1000) AS `over 1000`
  FROM sales_pipeline
 WHERE sales_pipeline.deal_stage = "Won"
 GROUP BY sales_pipeline.sales_agent

The first several rows of the resulting table would look like this:

For example:

SELECT sales_agent,
       COUNT(sales_pipeline.close_value) AS `number won`,
       COUNT(sales_pipeline.close_value)
FILTER(WHERE sales_pipeline.close_value > 1000) AS `number won > 1000`,
       AVG(sales_pipeline.close_value) AS `average of all`,
       AVG(sales_pipeline.close_value)
FILTER(WHERE sales_pipeline.close_value > 1000) AS `avg > 1000`
  FROM sales_pipeline
 WHERE sales_pipeline.deal_stage = "Won"
 GROUP BY sales_pipeline.sales_agent

The first several rows returned by the above query would look like:

Exercises (Continued from previous section)

Exercise 27

Write a query that returns the patient column and a count of all the allergies the patient has from allergies table. Group your results by patient, and order them by the number of allergies from greatest to least.

Try query

Exercise 28

Write a query that returns the patient column, the average of the value column relabeled as Avg BMI, the count of the value column relabeled as Number of readings , and the maximum value of the value column filtered for values over 30 and label it as Max Obese BMI. The query should be written against the observations_cleaned table and the results should all be for records where the description is “Body Mass Index”. Group your results by the patient column.

Try query