PostgreSQL: how to select 2 different accounts from the table when x = a and x = b

Question

Dear friend of Stackoverflowers,

How can I select 2 different counts from one table for scenarios:

x=a and x=b 

in particular, (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 later, 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) do not do what you probably think; count is an aggregate function that takes into account if a given value is not null in each line, and, seeing that you give it an integer literal, it will effectively count only the number of returned lines.

Answer 2

If I understand your requirement correctly, you can do as below

 SELECT description, SUM(CASE WHEN subdomain = type OR subdomain = subtype THEN 1 ELSE 0 END) FROM mapping GROUP BY description 

Answer 3

Using

 SELECT SUM( CASE WHEN type = 'whatever' THEN column_name_1 ELSE coumn_name_2 END AS column1, CASE WHEN subtype = 'whatever2' THEN column_name_3 ELSE coumn_name_4 END AS column2 )