Compute Count for multiple values in one query

Question

I have a table of answers like this:

ID ItemID Answer 
1   1      yes
2   1      no
3   1      yes
4   1      yes
5   2      no
6   2      yes
7   3      yes

I'd like to be able to provide an array of ItemId and compute the number of yes answers - number of no answers for each ItemID in the array.

I can do this for an individual item like this:

SELECT (SELECT count(ID) FROM table WHERE ItemID= <id> AND Answer = 'Yes') - (SELECT count(ID) FROM table WHERE ItemID= <id> AND Answer = 'No') AS difference

but how can I adapt this to work for multiple ids in one query?

My expected output, given the input [1,2] would look like

ItemID Difference 
  1      2
  2      0

Gordon Linoff · Accepted Answer · 2020-06-03 16:22:27Z

2

Just use conditional aggregatoin:

select itemid,
       count(*) filter (where answer = 'yes') as num_yes,
       count(*) filter (where answer = 'no') as num_no
from t
group by itemid;

You can include where itemid in (1, 2) (or something similar) if you want to limit this to particular items.

answered Jun 3, 2020 at 16:22

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Akhilesh Mishra · Accepted Answer · 2020-06-03 16:42:49Z

0

You can achieve with below mentioned query using case statement and aggregation:

try this

select itemid "ItemID", sum(case when answer = 'yes' then 1 else -1 end) "Difference"
from example 
where itemid in (1,2) --condition can be removed if required for all IDs
group by itemid

Demo

answered Jun 3, 2020 at 16:42

Akhilesh Mishra

6,1503 gold badges20 silver badges37 bronze badges

Collectives™ on Stack Overflow

Compute Count for multiple values in one query

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related