Which keyword Cannot be used with subquery that includes GROUP BY clause?

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.

Returning to a previous section, when we were working with aggregations, we used the aggregate function

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

1 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

sales_agentavgElease Gluck3614.9375Darcel Schlecht3304.3381088825213Rosalina Dieter3269.4861111111113Daniell Hammack3194.9912280701756James Ascencio3063.2074074074076Rosie Papadopoulos2950.8846153846152Wilburn Farren2866.181818181818Reed Clapper2827.974193548387Donn Cantrell2821.8987341772154

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

manageravgDustin Brinkmann1465.0107095046853Summer Sewald2372.886473429952Melvin Marxen2553.2086167800453Cara Losch2354.26875Celia Rouche2629.339344262295Rocco Neubert2837.257597684515

Though it’s not required by SQL, it is advisable to include all non-aggregated columns from your

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

2 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 will be 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 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:

sales_agentaccountsumAnna SnellingBetatech11,340Anna SnellingBioholding12,382Anna SnellingCancity1,496Anna SnellingCodehow22,479Anna SnellingCondax1,572Anna SnellingConecom4,186Anna SnellingDomzoom6,639Anna SnellingDontechi5,578Anna SnellingFinhigh1,762Anna SnellingFunholding5,654

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:

sales_agenttotalover 1000Boris Faz10170Maureen Marcano14996Vicki Laflamme221111Donn Cantrell158106Jonathan Berthelot17174Wilburn Farren5538Elease Gluck8032Cassey Cress163112James Ascencio13588Kami Bicknell17478Anna Snelling20868Violet Mclelland12233

As we saw in the aggregate functions section,

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

5 also limits the values in a query against which an aggregate function is run. FILTER is more flexible than

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

5 because you can use more than one FILTER modifier in an aggregate query while you can only use only one

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

5 clause.

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:

sales_agentnumber wonnumber > 1000average of allavg > 1000Rosalina Dieter72303,269.497,537.83Daniell Hammack114913,194.9993,869.47Gladys Colclough135882,560.553,791.19Rosie Papadopoulos78492,950.8854,559.16Kary Hendrixson209982,173.6754,071.13Cecily Lampkin107632,147.6653,439.74Lajuana Vencill127491,532.543,262.94Markita Hansen130682,529.174,512.37Moses Frase129511,606.0673,441.45Darcel Schlecht3492723,304.344,115.04Hayden Neloms107822,543.093,215.06Reed Clapper1551102,827.973,808.98Zane Levy161992,671.234,038.43Garret Kinder75462,636.974,041.37Niesha Huffines105561,685.342,747.96

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

Hint

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

2 the column

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

1 the

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

1 of the

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

1 column relabeled

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

2

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

3

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

6 a

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

2 of the

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

1 column relabeled

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

2

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

8

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

6 and the

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

0 of the column

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

1. FILTER the results to return only records

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

3 the

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

4 column is

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

5 and label the resulting column

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

2

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

7. All of these columns should be selected

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

4 the

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

9 table. Restrict your results to only include records

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

5 the GROUP BY1 column GROUP BY2 GROUP BY3.

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

6 the results

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

7 the

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

8 column.

Which keyword Cannot be used with subqueries that include GROUP BY clause?

Can we use GROUP BY in subquery?

Under what circumstances must you include a GROUP BY clause in a query?

Which of the following is not a grouping function Mcq?