Calculating mode (I)
Unfortunately, there is no function to calculate the mode, the most recurring value in a column. To calculate the mode:
- First, create a CTE containing an ordered count of values using
ROW_NUMBER() - Write a query using the CTE to pick the value with the highest row number
In this exercise, you will write the CTE needed to calculate the mode of OrderPrice.
This is a part of the course
“Intermediate SQL Server”
Exercise instructions
- Create a CTE
ModePricethat returns two columns (OrderPriceandUnitPriceFrequency). - Write a query that returns all rows in this CTE.
Hands-on interactive exercise
Have a go at this exercise by completing this sample code.
-- Create a CTE Called ModePrice which contains two columns
___ ___ (___, ___)
___
(
SELECT OrderPrice,
ROW_NUMBER()
OVER(PARTITION BY OrderPrice ORDER BY OrderPrice) AS UnitPriceFrequency
FROM Orders
)
-- Select everything from the CTE
___