SQL Interview Preparation—Intermediate Level/

...

/

Answer: Nested Subquery with HAVING and Aggregate Functions

Answer: Nested Subquery with HAVING and Aggregate Functions

Find a detailed explanation of using nested subqueries with the HAVING clause and aggregate functions.

We'll cover the following...

Solution

The solution is given below:

MySQL
-- Query to find months with maximum total sales for each category
SELECT   PC.CategoryName AS 'Category Name',
         S.Month AS Month,
         SUM(S.SalesAmount) AS 'Total Sales'
FROM     ProductCategories AS PC
JOIN     Sales S ON PC.CategoryID = S.CategoryID
GROUP BY PC.CategoryID, S.Month
HAVING   SUM(S.SalesAmount) = (
         SELECT MAX(TotalSales)
         FROM (SELECT CategoryID, Month, SUM(SalesAmount) AS TotalSales
               FROM Sales GROUP BY CategoryID, Month )
               AS MonthlyTotals
         WHERE MonthlyTotals.CategoryID = PC.CategoryID);

Code explanation

The explanation of the solution code is given below:

  • Lines 2–4: The SELECT statement selects the columns Category Name, and the total amount of sales made using the aggregate function with the SalesAmount column. We use AS to set an alias for the columns and tables.

  • Line 5: The data is retrieved from the ProductCategories table.

  • Line 6: The JOIN is performed between ProductCategories and Sales.

  • Line 7: The GROUP BY specifies the CategoryID and Month columns to group the data.

  • Lines 8–13: The HAVING clause filters the groups to keep only records with the maximum total sales for each category. The subquery calculates the maximum total sales for each category and month by grouping the sales data.

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

    • Line 8: The = operator keeps the records where the sum of SalesAmount for each group ...

Ask