Percentage row makes up of total where each row determine by group by clause

Question

I'm sure this is a stupid n00b question, but I have the following results table (which is created through a long-winded query I only half understand) and what I'd like is to be able to add in two columns that work out the row percentage relative to the total:

Sample results table:

CREATE TABLE #temp
(category varchar(30)
,count_people INT
,numpayments05 INT
,avegifts05 DECIMAL(4,2)
,value05 DECIMAL(18,2)
,donorvalue05 DECIMAL(18,2)
,giftvalue05 DECIMAL(18,2)
)
INSERT INTO #temp VALUES ('Single Gifts',124945,182458.00,1.46,28787034.10,230.40,157.77)
INSERT INTO #temp VALUES ('New Donor',67598,78799.00,1.17,5915450.93,87.51,75.07)
INSERT INTO #temp VALUES ('Reactivated',19853,27394.00,1.38,4348419.38,219.03,158.74)
INSERT INTO #temp VALUES ('2yrs consecutive',20604,31633.00,1.54,3556766.75,172.63,112.44)
INSERT INTO #temp VALUES ('3/4yrs consecutive',7536,13251.00,1.76,1827917.34,242.56,137.95)
INSERT INTO #temp VALUES ('5+yrs consecutive',9354,31381.00,3.35,13138479.70,1404.58,418.68)

The top row, 'single gifts' is the result from a rollup clause on the main query (not sure if that affects working out the percentage (which means the single gifts category is essentially a total value per column), and each category option derives from a group by clause applied to the query.

What I want is to be able to add in two new columns, 'percentage of value' and 'percentage of count_people', where percentage of value = row value05 / the total value05 (which is the value05 for the 'single gifts' category). The percentage of count_people would be the row value for count_people, again divided by the total (which is what is contained in the 'single gifts category row'

I'm on SQL Server 2005 in case that helps.

Thanks!

Answer 1

Do it in a second query using the first as input. Don't try modifying the first query to add these calculations, it's complex enough as it is.

Answer 2

This query will give you your percentages by row:

SELECT category, count_people, numpayments05, avegifts05, value05, donorvalue05, giftvalue05,
    value05 / SUM(CASE category WHEN 'Single Gifts' THEN value05 ELSE 0.0 END)
        OVER (PARTITION BY 1) AS [percentage of value],
    count_people / SUM(CASE category WHEN 'Single Gifts' THEN count_people ELSE 0.0 END)
        OVER (PARTITION BY 1) AS [percentage of count_people]
FROM #temp ;

Assuming that you create the two new columns in #temp with the following:

CREATE TABLE #temp
(category varchar(30)
,count_people INT
,numpayments05 INT
,avegifts05 DECIMAL(4,2)
,value05 DECIMAL(18,2)
,donorvalue05 DECIMAL(18,2)
,giftvalue05 DECIMAL(18,2)
,[percentage of value] decimal(5,2)
,[percentage of count_people] decimal(5,2)
)
INSERT INTO #temp VALUES ('Single Gifts',124945,182458.00,1.46,28787034.10,230.40,157.77, null, null)
INSERT INTO #temp VALUES ('New Donor',67598,78799.00,1.17,5915450.93,87.51,75.07, null, null)
INSERT INTO #temp VALUES ('Reactivated',19853,27394.00,1.38,4348419.38,219.03,158.74, null, null)
INSERT INTO #temp VALUES ('2yrs consecutive',20604,31633.00,1.54,3556766.75,172.63,112.44, null, null)
INSERT INTO #temp VALUES ('3/4yrs consecutive',7536,13251.00,1.76,1827917.34,242.56,137.95, null, null)
INSERT INTO #temp VALUES ('5+yrs consecutive',9354,31381.00,3.35,13138479.70,1404.58,418.68, null, null)

You could run the following update to save the percentages per row:

WITH cte AS
    ( SELECT category,
    value05 / SUM(CASE category WHEN 'Single Gifts' THEN value05 ELSE 0.0 END)
        OVER (PARTITION BY 1) AS [percentage of value],
    count_people / SUM(CASE category WHEN 'Single Gifts' THEN count_people ELSE 0.0 END)
        OVER (PARTITION BY 1) AS [percentage of count_people]
    FROM #temp
    )

UPDATE #temp
SET [percentage of value] = cte.[percentage of value],
    [percentage of count_people] = cte.[percentage of count_people]
FROM #temp
JOIN cte ON #temp.category = cte.category