Search code examples
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