CODEWITHSUNDEEP
sqlsql-server
slk
slk

Reputation: 15

Sum of TOP 5 PERCENT and sum of all as two columns

I have a table named All_Sources with the following columns:

svcmonth
source
member
cost

I'm trying to write a query that will return the sum of the costs of the TOP 5 PERCENT of members as the first column, and the sum of the costs of all members in the table as the second column. I'm able to get these two numbers individually with the following two queries:

SELECT SUM(t1.fivepercentcost)
FROM
(SELECT
TOP 5 PERCENT member, 
SUM(cost) AS fivepercentcost
FROM dbo.All_Sources
WHERE svcmonth = '2013, 01' AND source = 'HC'
GROUP BY member
ORDER BY SUM(cost) DESC) t1

and

SELECT SUM(cost) AS 
FROM dbo.All_Sources
WHERE svcmonth = '2013, 01' AND source = 'HC'

However, where I'm stuck is trying to put these two queries together so that the two results will display side by side in two columns. I've been trying to work with CTEs and the OVER() function without any luck. Any help is greatly appreciated.

Edit: Sample Data

svcmonth   source   member   cost
2012, 12   HC       1        50
2012, 12   BA       1        40
2013, 01   HC       1        55
2013, 01   HC       2        100
2013, 01   BA       1        50
2013, 02   BA       2        45

Upvotes: 1

Views: 465

Answers (2)

Khan
Khan

Reputation: 18162

This should get you there. The trick would be to do an OVER and PARTITION BY (SELECT NULL) so that you can do in inline SUM of the table.

SELECT  SUM(fivepercentcost) AS fivepercentcost, 
        Total
FROM (
    SELECT TOP 5 PERCENT 
            member,
            SUM(cost) AS fivepercentcost,
            Total
    FROM (
        SELECT  member,
                cost, 
                SUM(cost) OVER(PARTITION BY (SELECT NULL)) AS Total     
        FROM    dbo.All_Sources
        WHERE   svcmonth = '2013, 01' 
            AND source = 'HC'
    ) A
    GROUP BY member, Total
    ORDER BY SUM(cost) DESC
) A
GROUP BY Total

Upvotes: 1

Giannis Paraskevopoulos
Giannis Paraskevopoulos

Reputation: 18411

Declare two variables, load them individually and select them both as follows:

DECLARE @TOP5 AS FLOAT
DECLARE @ALL AS FLOAT

SELECT @TOP5 = SUM(t1.fivepercentcost)
FROM
(SELECT
TOP 5 PERCENT member, 
SUM(cost) AS fivepercentcost
FROM dbo.All_Sources
WHERE svcmonth = '2013, 01' AND source = 'HC'
GROUP BY member
ORDER BY SUM(cost) DESC) t1

SELECT @ALL = SUM(cost) AS 
FROM dbo.All_Sources
WHERE svcmonth = '2013, 01' AND source = 'HC'

SELECT @TOP5, @ALL

Upvotes: 0

Related Questions