PostgreSQL: How to select 2 different counts from table when x=a and x=b

Question

Dear fellow Stackoverflowers,

How may one select 2 different counts from the same table for scenarios:

x=a and x=b

specifically, (WHEN type = subdomain) AND (WHEN subtype = subdomain)?

to add them together to create a 'totalcount'?

My attempt (for your reference):

SELECT description, type, count(1), subtype, count(2) 
FROM mapping, (dotcom WHERE type = subdomain) AS typecount, 
     (dotcom WHERE subtype = subdomain) AS subtypecount 
GROUP BY description, type, subtype 
HAVING count(1)>1 
AND count(2)>1 
ORDER BY count(*) 
DESC LIMIT 10

Second attempt:

SELECT description
FROM mapping, SUM(WHEN subdomain = type OR subdomain = subtype) AS count(1)
GROUP BY description, type, subtype 
HAVING count(1)>1 
ORDER BY count(*) 
DESC LIMIT 10

Answer 1

If you have Postgres 9.4 or newer, you can use FILTER, which is much easier to read than the CASE WHEN ... END syntax.

SELECT
  description, 
  type,
  COUNT(*) FILTER (WHERE subdomain = type) AS typecount,
  subtype,
  COUNT(*) FILTER (WHERE subtype = subdomain) AS subtypecount
FROM mapping
GROUP BY description, type, subtype;

It should be noted that count(1) and count(2) doesn't do what you probably think it does; count is an aggregate function that counts if the given value is not null in each row, and seeing as you're giving it an integer literal, it will effectively only count the number of rows returned.