CODEWITHSUNDEEP
sqldatabasegroup-bycountwindow-functions
Daniel Violante
Daniel Violante

Reputation: 27

Count number of certain value that shows per ID

I want this query to display the amount of Yes and No answers in two separate column per ID (there will be multiple entries per ID). How can I achieve this?

Thanks for your help!

select 
    id, 
    Yes_No,
    (select count(Yes_No) from Survey where Yes_No = 'Y') as '# of Yes',
    (select count(Yes_No) from Survey where Yes_No ='N') as '# of No'
from Survey;

Expected Result Image

Upvotes: 2

Views: 1811

Answers (3)

Caius Jard
Caius Jard

Reputation: 74660

If no analytic functions were available in your db, I'd count in a couple of subqueries. I typically avoid doing correlated queries in the select list:

SELECT 
  s.id, 
  s.Yes_No,
  COALESCE(y.countYes, 0) as countYes,
  COALESCE(n.countNo, 0) as countNo
FROM
  Survey s
  LEFT JOIN
  (SELECT id, COUNT(*) as countYes from Survey WHERE Yes_No = 'Y' GROUP BY id ) y
  ON y.id = s.id

  LEFT JOIN
  (SELECT id, COUNT(*) as countNo from Survey WHERE Yes_No = 'N' GROUP BY id ) n
  ON n.id = s.id;

This is fairly easy to understand - the count and group is done and joined back. we left join in case there are no Y or no N for a particular ID, so the rows don't disappear from the results. It might be more performant to do this though, so we don't group and count the table twice (one for Y, one for N

SELECT 
  s.id, 
  s.Yes_No,
  c.countYes,
  c.countNo
FROM
  Survey s
  INNER JOIN
  (
    SELECT 
      id, 
      COUNT(CASE WHEN Yes_No = 'Y' THEN 1 END) as countYes,
      COUNT(CASE WHEN Yes_No = 'N' THEN 1 END) as countNo
    FROM Survey  
    GROUP BY id
   ) c
  ON c.id = s.id

This and the following one use a conditional counting technique that ill explain more later. This one can be safely inner joined because every ID from survey will have something matching in the subquery. It may perform better due to a single aggregating operation

If your database supports analytic/window functions it gets easier:

SELECT 
  s.id, 
  s.Yes_No,
  COUNT(CASE WHEN s.Yes_No = 'Y' THEN 1 END) OVER(PARTITION BY s.id) as countYes,
  COUNT(CASE WHEN Yes_No = 'N' THEN 1 END) OVER(PARTITION BY s.id) as countNo
FROM
  Survey s

(At the time I started writing this, Gordon's query was using conditional aggregation and collapsing the rows; it's since been changed to something more or less the same)

The way this one works is to use a case when to convert the Yes_No values into 1 or NULL. Count then counts the 1 as 1 (it counts anything non null as 1) and null as 0. The over/partition by clause causes the count to restart per id.

SQL server, oracle, Postgres, MariaDB 10 and mysql 8 support these. SQLite May support it. Access and primitive versions of mysql don't - use the joined grouped totals form for those

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1270401

Use conditional aggregation with window functions:

select id, Yes_No,
       sum(case when Yes_No = 'Y' then 1 else 0 end) over (partition by id) as yes_cnt,
       sum(case when Yes_No = 'N' then 1 else 0 end) over (partition by id) as no_cnt
from Survey;

Upvotes: 0

GMB
GMB

Reputation: 222582

You are quite close. You just need to correlate the inline queries in the SELECTclause to the current id, like:

select 
    id, 
    Yes_No,
    (select count(Yes_No) from Survey where Yes_No = 'Y' and id = s.id) as '# of Yes',
    (select count(Yes_No) from Survey where Yes_No ='N' and id = s.id) as '# of No'
from Survey s;

Note: if you RDBMS supports windows functions, there is a simpler option to use a conditional window sum:

select 
    id, 
    Yes_No,
    SUM(CASE WHEN Yes_No = 'Y' THEN 1 ELSE 0 END) OVER(PARTITION BY id) as '# of Yes',
    SUM(CASE WHEN Yes_No = 'N' THEN 1 ELSE 0 END) OVER(PARTITION BY id) as '# of No'
from Survey;

Upvotes: 2

Related Questions