SQL Interview Preparation—Intermediate Level/

...

/

Answer: Subquery with HAVING and Aggregate Functions

Answer: Subquery with HAVING and Aggregate Functions

Find a detailed explanation of using subqueries with the HAVING clause and AVG() function.

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  ...