SQL Tutorial - Window Functions - Ranking with Group By

Another fantastic SQL Tutorial brought to you by BeardedDev.

T-SQL Querying

T-SQL Fundamentals

Microsoft SQL Server 2012 High-Performance T-SQL Using Window Functions

In this tutorial we explore ranking functions as part of Window Functions using the Group By clause within SQL Server.

In this example we go through how to rank customers based on the sum of the orders they have placed with us.

The window function example also covers the difference between using Rank and Dense_Rank.

SQL
SELECT
Sales_Cust_Id
, SUM(Sales_Total) AS Total
FROM dbo.Sales_2
GROUP BY Sales_Cust_Id
ORDER BY Total DESC

SELECT
Sales_Cust_Id
, SUM(Sales_Total) AS Total
, RANK() OVER(ORDER BY SUM(Sales_Total) DESC) AS rnk
, DENSE_RANK() OVER(ORDER BY SUM(Sales_Total) DESC) AS dnse
FROM dbo.Sales_2
GROUP BY Sales_Cust_Id
ORDER BY rnk

[sarahemslie]

Thank you BeardedDev SEVERAL times over. I can barely concentrate for the sound of pennies dropping thanks to you.

[dpyioyg]

A couple of years late, but thanks! This is way faster than achieving the same result with cte and join.

[anitkunwar]

You are just awesome. You are LITERALLY changing my life. Thanks!

[frenkyb.]

Great video, thanks a lot. Watching this I've got the idea that probably - with window functions - you can achieve the same result (getting sum of total amount spend and rank total amount spend) without using group by?

[sandeepgnv]

Hi
BeardedDev, where can I get the data set which was used by you?

[bahubalichanduwad]

Can't we use partition by instead of using group by on sales_cust_id ?

[edimathomas-crkm]

What if you want to return only total sales that rank from 1 to 4?

[oualidlaib]

Why you didn't use Total instead of SUM(Sales_Total) ?
Because you've named SUM(Sales_Total) AS Total.
And Thank you on advance.