SQL Interview Preparation—Intermediate Level/

...

/

Answer: Aggregate Window Function

Answer: Aggregate Window Function

Find a detailed explanation of using aggregate window functions in SQL.

We'll cover the following...

Solution

The solution is given below:

MySQL
Saved
/*Query to find the count of sales for each month*/
SELECT   DISTINCT Month,
         COUNT(*) OVER (PARTITION BY Month) AS 'Total Sales'
FROM     Sales
ORDER BY FIELD(Month, "January", "February", "March", "April", "May");

Code explanation

The explanation of the solution code is given below:

  • Lines 2–3: The SELECT statement selects the columns Month and Total sales. The Total sales column retrieves the count of the product in each category using the aggregate window function. We use AS to set an alias for the columns.

  • Line 4: The data is retrieved from the Sales table.

  • Line 5: The ORDER BY clause sorts the results by the Month column in custom order of months using the FIELD() function.

Recalling relevant concepts

We have covered the following concepts in this question:

  • Selective columns

  • Aliases

  • Aggregate functions

  • Sorting the results ...