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?

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

3 Answers 3

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
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.
    – tkmagnet
    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
    – SergeyA
    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.
    – tkmagnet
    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.
    – tkmagnet
    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!

1
  • That's the same answer as mine, just without showing the original aggregation 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;
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()?
    – Vérace
    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) Sep 9, 2023 at 20:24

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.