GROUP BY and FILTER (2024)

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

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

GROUP BY

Returning to a previous section, when we were working with aggregations, weused the aggregate function AVG to find out the average deal size. If wewanted to know the average value of the deals won by each sales person fromhighest 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 byrunning 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

Though it’s not required by SQL, it is advisable to includeall non-aggregated columns from your SELECT clause in your GROUP BY clause.If you don’t, there are cases where the query will return the desired results,there are also instances where a random value from the non-aggregated row willbe used as the representative for all the values returned by the query.

For example, let’s say you wanted to know the average deal by sales agent foreach 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 timeand 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 looklike 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 aggregationsagainst a limited set of the values in a column you could use the FILTERkeyword. For example, if you wanted to know both the number of deals won bya 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:

As we saw in the aggregate functions section, WHERE alsolimits the values in a query against which an aggregate function is run.FILTER is more flexible than WHERE because you can use more than oneFILTER modifier in an aggregate query while you can only use only one WHEREclause.

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)

There are two ways to do these exercises. The first is to use the “Try query” links to test your queries without saving them. The second is to create a data.world project and save your queries to it. If you are reading this documentation and completing the exercises as a tutorial, you will need to create your own project to save your work. Details and instructions are in the SQL tutorial which has instructions for setting up your project and links to all the current exercises.

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