sqlsql-serverdml

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

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
Solution

  • 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