SQL Exercises: Counts the customers with grades over New York average

SQL SUBQUERY : Exercise-8 with Solution

8. From the following tables write a SQL query to count the number of customers with grades above the average in New York City. Return grade and count.

customer_id  cust_name     city        grade       salesman_id
-----------  ------------  ----------  ----------  -----------
3002         Nick Rimando  New York    100         5001
3005         Graham Zusi   California  200         5002
3001         Brad Guzan    London      100         5005
3004         Fabian Johns  Paris       300         5006
3007         Brad Davis    New York    200         5001
3009         Geoff Camero  Berlin      100         5003
3008         Julian Green  London      300         5002
3003         Jozy Altidor  Moncow      200         5007

Sample Solution:

-- Selecting the 'grade' column and the count of rows from the 'customer' table
SELECT grade, COUNT(*)
-- Specifying the table to retrieve data from ('customer')
FROM customer
-- Grouping the results by the 'grade' column
GROUP BY grade
-- Applying a filter to the grouped results, including only those with 'grade' greater than the average 'grade' for customers in 'New York'
HAVING grade >
    -- Subquery: Selecting the average 'grade' from the 'customer' table where 'city' is 'New York'
    (SELECT AVG(grade)
     FROM customer
     WHERE city = 'New York');

Output of the Query:

grade	count
200		3
300		2

Explanation:

The said SQL query selects the "grade" and the count of all rows (*) from the 'customer' table and groups them by the "grade" column. It also only includes the groups where the "grade" is greater than the average "grade" from the 'customer' table where the "city" is equal to 'New York'.
In other words, it is selecting the grade and the count of customers with that grade, where the grade is above the average grade of customers living in New York, grouped by that grade.

Visual Explanation:

Practice Online

Sample Database: inventory

Query Visualization:

Duration:

Rows:

Cost:

Contribute your code and comments through Disqus.

Previous SQL Exercise: The customer whose ID is 2001 is below Mc Lyons.
Next SQL Exercise: Find salesmen who earned the maximum commission.