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

Answer: Nested Subquery with HAVING and Aggregate Functions

Explore how to write SQL queries using nested subqueries combined with HAVING clauses and aggregate functions. This lesson helps you understand grouping data, filtering groups based on aggregates, and applying joins. You will also learn alternate methods like CTEs and window functions to solve similar problems.

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