Single SQL query for getting count based of 2 condition in same table

user_2040352 0
Viewed 131

I have data like this

Table Data

Now I need a single query to get count of id where Info is 'Yes' and count of id which are in both 'yes' and 'no'

Single query for:

SELECT COUNT(id) FROM table WHERE info = 'yes'

and

SELECT COUNT(id) FROM table WHERE info = 'yes' AND info = 'No'

Since

Id having Yes are 7 (1,2,3,4,5,6,7)
and Id having and Yes and No both vaules are only 3 (1,4, 6) 
it should give me id_as_yes = 7 and id_as_yes_no = 3
dml sql-server sql
4 Answers

You can do it with aggregation and window functions:

SELECT DISTINCT 
       SUM(MAX(CASE WHEN info = 'yes' THEN 1 ELSE 0 END)) OVER () id_as_yes,
       COUNT(CASE WHEN COUNT(DISTINCT info) = 2 THEN 1 END) OVER () id_as_yes_no
FROM tablename
GROUP BY id

See the demo.
Results:

> id_as_yes | id_as_yes_no
> --------: | -----------:
>         7 |            3
user_104988280

You need conditional aggregation.

Select id, 
       Count(case when info = 'y' then 1 end) as y_count,
       Count(case when info = 'y' and has_n = 1 then 1 end) as yn_count
  From (SELECT id, info,
               Max(case when info = 'no' then 1 end) over (partirion by id) as has_n
         From your_table) t
user_115656290

You can do this without a subquery. This relies on the observation that the number of ids that are "no" only is:

count(distinct id) - count(distinct case when info = 'yes' then id end)

And similarly for the number of yeses. So, the number that have both is the number of ids minus the number of no only minus the number of yes only:

select count(distinct case when info = 'yes' then id end) as num_yeses,
       (count(distinct id) -
        (count(distinct id) - count(distinct case when info = 'yes' then id end)) -
        (count(distinct id) - count(distinct case when info = 'no' then id end))
       )
from t;
user_11440350

This should do the trick...it's definitely not efficient or elegant, but no null value aggregate warnings

dbFiddle link

Select
    (select select count(distinct id) from mytest where info = 'yes') as yeses
    ,(select count(distinct id) from mytest where info = 'no' and id in (select distinct id from mytest where info = 'yes' )) as [yes and no]
user_79891350
Related