SQL select row where column SUM is largest (with two fields in GROUP BY)
Here is my main query (SQL Server):
SELECT projectID, businessID, sum(number) AS summ
FROM table
GROUP BY projectID, businessID
ORDER BY projectID, sum(number) DESC
which creates a table like this:
Project ID Business ID Summ
1 1 63
1 2 32
1 3 6
2 3 45
2 1 44
2 2 3
I want to grab the project id and business id where the Summ column is the largest for each project id. So, lines 1 and 4 are shown in the above example. How can I customize the original query to do this?
+3
source to share
3 answers
You can use analytic functions:
SELECT projectID,
businessID,
summ
FROM(SELECT projectID,
businessID,
SUM(number) AS summ,
ROW_NUMBER() OVER (PARTITION BY projectID
ORDER BY SUM(number) DESC) AS rn
FROM table
GROUP
BY projectID,
businessID
) t
WHERE rn = 1
ORDER
BY projectID;
Hope it helps.
+1
source to share
You need a second aggregation or analytic function. Here's how to do it with a second aggregation:
WITH sums as (
SELECT projectID, businessID, sum(number) AS summ
FROM table
GROUP BY projectID, businessID
)
SELECT sums.projectId, sums.businessId, sums.summ
FROM
(
SELECT projectID, MAX(summ) as ms
FROM sums
GROUP BY projectID
) ms
JOIN sums
ON sums.projectId = ms.projectId AND sums.summ = ms.ms
You need this approach on a system that does not support analytic functionality, luckily for you, does not include SQL Server.
0
source to share