# Percentage based on counting 2 values per ID

I have table1 with 2 ID's and 2 values per ID (Y,N). I can count the values by the following query:

select id
,count(*) as "total"
,choice
from table1
where id in (1,8)
group by id, choice

I get the following results:
id | total | choice
1 | 55 | N
1 | 17 | Y
8 | 319 | N
8 | 123 | Y

Is there a way to write a query that will give me the percentages of each value (Y,N) for each id?

Desired result:
id | total | choice | percent
1 | 55 | N | 0.236
1 | 17 | Y | 0.764
8 | 319 | N | 0.722
8 | 123 | Y | 0.278

``````
DROP TABLE IF EXISTS #data;
CREATE TABLE #data ( id int NOT NULL, total INT NULL, choice char(1) NULL );
INSERT INTO #data VALUES
(1 , 55 , 'N'     ),
(1 , 17 , 'Y'     ),
(8 , 319 , 'N'    ),
(8 , 123 , 'Y'    )

;WITH cte_totals AS (
SELECT
id
,choice
,total
,SUM(total) OVER(PARTITION BY id) AS id_total
FROM #data
)
SELECT
id
,SUM(total) as "total"
,choice
,CAST(SUM(total) * 100.0 / MAX(id_total) AS decimal(3, 1)) AS "percent"
FROM cte_totals
GROUP BY id, choice
ORDER BY id, choice
``````

You don't need the CTE - it can be done in a single query:

``````SELECT d.id
, total = sum(d.total)
, d.choice
, [percent] = cast(1.0 - (sum(d.total) * 1.0 / sum(sum(d.total)) OVER(PARTITION BY d.id)) AS decimal(5,3))
FROM #data    d
GROUP BY
d.id
, d.choice
ORDER BY
d.id
, d.choice;
``````

It is one of the few areas where you can use an aggregate within the windowing function - and I corrected the percentage calculation.