Getting the MAX() of COUNT() aggregates
-
Hello,
I've been searching on how to do this but am coming up empty. I am sure there is a simple, elegant solution, but my brain is not seeing it. I have a query:
SELECT d.state, vs.modelID As Model, sum(vs.totalSalesCount) As TotalCarsSold
FROM Dealerships d
inner join VehicleSales vs on vs.dealershipID = d.dealershipID
inner join Vehicles v on vs.modelID = v.modelID
WHERE v.vehicleClass = 'SUV'
GROUP BY d.state, vs.modelID
ORDER BY d.state, vs.modelID
And I need to get the bestselling SUV model for each state. The result set should return 50 records (one per state).
-
Here's one way you could do it:
WITH summed_and_ranked AS(
SELECT d.state,
vs.modelID As Model,
sum(vs.totalSalesCount) As TotalCarsSold,
rnk=RANK() OVER (PARTITION BY d.state ORDER BY SUM(vs.totalSalesCount) DESC)
FROM Dealerships d
inner join VehicleSales vs on vs.dealershipID = d.dealershipID
inner join Vehicles v on vs.modelID = v.modelID
WHERE v.vehicleClass = 'SUV'
GROUP BY d.state, vs.modelID
)
SELECT state,Model, TotalCarsSold
FROM summed_and_ranked
WHERE rnk=1
ORDER BY state ASC;?
Cheers!
-
You've already done most of the work. All you need is a query to run over the data you already have. I added a function to your query and removed to ORDER BY clause. Then all you have to to is simply query the top-seller in each state.
WITH cte AS (SELECT d.state, vs.modelID As Model, sum(vs.totalSalesCount) As TotalCarsSold,
RN = ROW_NUMBER() OVER(PARTITION BY d.State ORDER BY TotalCarsSold DESC)
FROM Dealerships d
inner join VehicleSales vs on vs.dealershipID = d.dealershipID
inner join Vehicles v on vs.modelID = v.modelID
WHERE v.vehicleClass = 'SUV'
GROUP BY d.state, vs.modelID
)
SELECT State, Model, TotalCarsSold
FROM cte
WHERE RN = 1
ORDER BY State;
There are other windowing functions you could use here as well. RANK and DENSE_RANK could work just as well as ROW_NUMBER.
Edit: Removed ModelID from the partition. Thanks, Jacob.
-
Ed Wagner (2/7/2016)
You've already done most of the work. All you need is a query to run over the data you already have. I added a function to your query and removed to ORDER BY clause. Then all you have to to is simply query the top-seller in each state.WITH cte AS (SELECT d.state, vs.modelID As Model, sum(vs.totalSalesCount) As TotalCarsSold,
RN = ROW_NUMBER() OVER(PARTITION BY d.State, vs.ModelID ORDER BY TotalCarsSold DESC)
FROM Dealerships d
inner join VehicleSales vs on vs.dealershipID = d.dealershipID
inner join Vehicles v on vs.modelID = v.modelID
WHERE v.vehicleClass = 'SUV'
GROUP BY d.state, vs.modelID
)
SELECT State, Model, TotalCarsSold
FROM cte
WHERE RN = 1
ORDER BY State;
There are other windowing functions you could use here as well. RANK and DENSE_RANK could work just as well as ROW_NUMBER.
I used RANK under the assumption that if there were ties all the tied models would want to be seen, but that wasn't specified 🙂
Also, shouldn't the ModelID be removed from the PARTITION BY? Otherwise all rows will have a ROW_NUMBER of 1, since you're then partitioning by the same thing the SUM is grouping by.
Cheers!
-
Jacob Wilkins (2/7/2016)
Ed Wagner (2/7/2016)
You've already done most of the work. All you need is a query to run over the data you already have. I added a function to your query and removed to ORDER BY clause. Then all you have to to is simply query the top-seller in each state.WITH cte AS (SELECT d.state, vs.modelID As Model, sum(vs.totalSalesCount) As TotalCarsSold,
RN = ROW_NUMBER() OVER(PARTITION BY d.State, vs.ModelID ORDER BY TotalCarsSold DESC)
FROM Dealerships d
inner join VehicleSales vs on vs.dealershipID = d.dealershipID
inner join Vehicles v on vs.modelID = v.modelID
WHERE v.vehicleClass = 'SUV'
GROUP BY d.state, vs.modelID
)
SELECT State, Model, TotalCarsSold
FROM cte
WHERE RN = 1
ORDER BY State;
There are other windowing functions you could use here as well. RANK and DENSE_RANK could work just as well as ROW_NUMBER.
I used RANK under the assumption that if there were ties all the tied models would want to be seen, but that wasn't specified 🙂
Also, shouldn't the ModelID be removed from the PARTITION BY? Otherwise all rows will have a ROW_NUMBER of 1, since you're then partitioning by the same thing the SUM is grouping by.
Cheers!
Aahhhhh! :crazy: Thanks for pointing that out. You're exactly right. I shouldn't have included ModelID in the partition. That's what I get for not creating a table and testing it. I'll edit my post. Thanks again.
-
Thank you Jacob and Ed both for your help! Worked flawlessly!
-
Chad Caswell (2/7/2016)
Thank you Jacob and Ed both for your help! Worked flawlessly!Glad they worked for you. Thanks for the feedback.
-
Why are we making it complicated?SELECT TOP(1) WITH TIES
d.state,
vs.modelID As Model,
SUM(vs.totalSalesCount) As TotalCarsSold
FROM Dealerships d
inner join VehicleSales vs on vs.dealershipID = d.dealershipID
inner join Vehicles v on vs.modelID = v.modelID
WHERE v.vehicleClass = 'SUV'
GROUP BY d.state, vs.modelID
ORDER BY TotalCarsSold DESC;
Misread the post. I need some coffee.
Viewing 8 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply