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
___This exercise is part of the course
Intermediate SQL Server
In this course, you will use T-SQL, the flavor of SQL used in Microsoft's SQL Server for data analysis.
In the final chapter of this course, you will work with partitions of data and window functions to calculate several summary stats and see how easy it is to create running totals and compute the mode of numeric columns.
Exercise 1: Window functions in T-SQLExercise 2: Window functions with aggregations (I)Exercise 3: Window functions with aggregations (II)Exercise 4: Common window functionsExercise 5: Do you know window functions?Exercise 6: First value in a windowExercise 7: Previous and next valuesExercise 8: Increasing window complexityExercise 9: Creating running totalsExercise 10: Assigning row numbersExercise 11: Using windows for statistical functionsExercise 12: Calculating standard deviationExercise 13: Calculating mode (I)Exercise 14: Calculating mode (II)What is DataCamp?
Learn the data skills you need online at your own pace—from non-coding essentials to data science and machine learning.