Combine two SELECT queries into one

Question

I have two queries in which I need only the number of shared records, but the only difference in the queries is a single field value.

Example

SELECT COUNT(*) AS group_a
FROM tbl
WHERE category = 'value_a'

SELECT COUNT(*) AS group_b
FROM tbl
WHERE category = 'value_b'

How can I get something like this: (pseudo)

SELECT COUNT(*) AS group_a, COUNT(*) AS group_b
FROM tbl
WHERE category IN ('value_a', 'value_b')

But the results are like this.

group_a , group_b
56, 101

I thought the CASE statement in the query would filter two, but how to implement it? or is there a better way?

I am doing UNION right now, but would like to know if I can return one record with two results

Answer 1

select sum(case when category = 'value_a' then 1 else 0 end) as group_a,
       sum(case when category = 'value_b' then 1 else 0 end) as group_b
    from tbl
    where category in ('value_a', 'value_b')

Answer 2

select  sum(case when category = 'value_a' then 1 else 0 end) group_a,
        sum(case when category = 'value_b' then 1 else 0 end) group_b
from tbl

Answer 3

SELECT category,COUNT(*) FROM tbl
GROUP BY category;

.

SELECT category,COUNT(*) FROM tbl
WHERE category IN ('value_a', 'value_b')
GROUP BY category; 

Answer 4

. COUNT:

SELECT COUNT(category = 'value_a' OR NULL) AS group_a, COUNT(category = 'value_b' OR NULL) AS group_b FROM tbl;

PostgreSQL COUNT aggregate has complex syntax, as I showed. Please note that the value is OR NULLvery important, since COUNT only counts rows for which the condition category = '...' OR NULLgives a non-NULL response.

Answer 5

Just for fun:

SELECT * 
FROM 
(
    SELECT category
    FROM tbl 
) subquery
PIVOT
(
    COUNT(category)
    FOR category IN ([value_a],[value_b])
) AS piv