The percentage line consists of the total amount, where each line is determined by the group according to the article

Question

I'm sure this is a stupid question n00b, but I have the following results table (which is created through a long query that I only understand halfway), and I would like it to be able to add in two columns that determine the percentage of rows relative to the total quantities:

Example result 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 line “single gifts” is the result of the rollup clause of the main query (not sure if this affects the percentage processing (which means that the category of single gifts is essentially the total cost per column), and each category is selected from the group by clause applied to request.

, : " " " count_people", value = row value05/total value05 ( 05 " " '). count_people count_people, ( ,

SQL Server 2005 , .

!

Answer 1

:

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 ;

, #temp :

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)

, :

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

Answer 2

, . , , , .