Search⌘ K
AI Features
Home
Courses
SQL Interview Preparation—Intermediate Level

Answer: Subquery with HAVING and Aggregate Functions

Explore how to write SQL queries using subqueries combined with HAVING clauses and aggregate functions like AVG and SUM. This lesson teaches you to filter grouped data effectively, join tables, and apply different techniques such as CTEs and derived tables to solve common interview challenges involving sales data analysis.

We'll cover the following...

Solution

The solution is given below:

MySQL
-- The query to find employees whose average sales is more than the average sales for the category
SELECT   E.EName AS 'Employee Name', AVG(S.SalesAmount) AS 'Average Sales'
FROM     Employees AS E
JOIN     Sales S ON E.EID = S.EID
GROUP BY E.EName, S.CategoryID
HAVING   AVG(S.SalesAmount) > (
         SELECT AVG(SalesAmount) 
         FROM   Sales AS S2 
         WHERE  S2.CategoryID = S.CategoryID);

Code explanation

The explanation of the solution code is given below:

  • Line 2: The SELECT statement selects the columns EName and Average Sales. The Average Sales column calculates the average of the SalesAmount using the aggregate window function. We use AS to set an alias for the columns and tables.

  • Line 3: The data is retrieved from the Employees table.

  • Line 4: The JOIN is performed between Employees and Sales.

  • Line 5: The GROUP BY specifies the EName and CategoryID columns to group the data.

  • Lines 6–9: The HAVING clause filters the groups to keep only those that match a specific condition.

    • Line 6: The > operator checks if the average SalesAmount is higher than the average SalesAmount of the same category.

    • Lines 7–9: The subquery calculates the average of SalesAmount using the AVG() function. The data is retrieved from the  ...