2

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?
(id1: 55/55+17 (N), 17/55+17 (Y), etc.)

Desired result:

id total choice percent
1 55 N 0.236
1 17 Y 0.764
8 319 N 0.7222
8 123 Y 0.278

Would I still need to use count(*) in the query?

Improve this question
1
  • Please tag your question with the DBMS for which you need the solution. Commented Sep 7, 2023 at 21:47

3 Answers 3

Reset to default
1 
create table #table2 (id int, choice char(1)) insert into #table2 values (1,'N') go 55 insert into #table2 values (1,'Y') go 17 insert into #table2 values (8,'N') go 319 insert into #table2 values (8,'Y') go 123 select id ,count(*) as "total" ,choice from #table2 where id in (1,8) group by id, choice select t.id,t.total,t.choice, (1.*total/grandtotal) as [percent] from (select id ,count(*) as "total" ,choice from #table2 where id in (1,8) group by id, choice ) as t join ( select id ,count(*) as "grandtotal" from #table2 where id in (1,8) group by id ) as g on t.id=g.id
Improve this answer
4
  • Unfortunately, I have read-only access to the db. I don't believe I'll be able to create a table to query from. Commented Sep 7, 2023 at 21:31
  • 1
    you don't need to create a table. replace query in FROM section with your query with GROUP BY id, choice and query in JOIN section with GROUP BY ID Commented Sep 7, 2023 at 21:42
  • If I needed to expand or change the id's to roll up more than just 1 and 8, would I need to include those in the "where id in" statement or is there another way to run a query where the id's could change depending on the need? For example, say the report called to return only id's that have any value in them? The id's values may change depending on if/when the choices change between Y, N, or some other value. The changes to choices does have an open text option, but would be limited to about 40 different choices. Commented Sep 8, 2023 at 16:25
  • Actually got this figured out. I had an issue with including certain criteria outside of what the query was presenting. Commented Sep 8, 2023 at 20:44
1

To solve this, I did the following (all of the code below is available on the fiddle here):

CREATE TABLE tab ( id INT NOT NULL, total INT NOT NULL, choice BOOLEAN NOT NULL, CONSTRAINT id_choice_uq UNIQUE(id, choice) -- PK? );

Populate it with your data:

INSERT INTO tab (id, total, choice) VALUES (1, 55, 'N'), (1, 17, 'Y'), (8, 319, 'N'), (8, 123, 'Y');

and the I ran the following SQL:

SELECT id, total, SUM(total) OVER (PARTITION BY id ORDER BY id) AS "total by id", ROUND((total::NUMERIC/SUM(total) OVER (PARTITION BY id ORDER BY id)) * 100, 2) AS percentage FROM tab WHERE id IN (1, 8) GROUP BY id, total;

Result:

id total total by id percentage 1 17 72 23.61 1 55 72 76.39 8 319 442 72.17 8 123 442 27.83

This solution uses window funtions (manual) which are very powerful and will repay any time and effort spent learning them many times over! p.s. welcome to dba.se!

Improve this answer
1
  • That's the same answer as mine, just without showing the original aggregation Commented Sep 9, 2023 at 20:25
0

Use a windowed sum over the aggregated count

select id ,count(*) as "total" ,choice ,count(*) * 1.0 / sum(count(*)) over (partition by id) as "percent" from table1 where id in (1,8) group by id, choice;
Improve this answer
2
  • Take a look at the last snippet in the fiddle here - from my answer - looks like it's SUM() you want and not COUNT()? Commented Sep 9, 2023 at 8:51
  • @Vérace The original query was using aggregation to get the total column, so it's a SUM over that COUNT. In your example you've just created a table with total already calculated, so you need total * 1.0 / SUM(total) OVER (partition by id) Commented Sep 9, 2023 at 20:24

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.