4

This question already has an answer here:

I have a table main with the following rows:

 id |  name  | rank
----+--------+------
  1 | Ali    | a
  2 | Sami   | b
  3 | Khan   | c
  4 | Kamran | d
  5 | Imran  | e
  6 | Asad   | a
  7 | Nawid  | v
  8 | Jamil  | c
  9 | Usman  | j

I want to count rows with certain values in column rank. For example, I want to group values as follows:

  • Values (a,b,v) should come in one group by name myvalues.
  • Values (c,d,j) should come in another group by name yourvalues.
  • Value (e) should come to another group by name extravalues.

My desired result:

myvalues | yourvalues | extravalues
   4     |     4      |      1

myvalues counts 4 because it consists of (a,b,v). There are 2 occurrences of a, 1 occurrence of b and 1 of v - a total of 4.
The same is the case with yourvalues which consists of (c,d,j) and the occurrences of these values makes a total of 4.
And the last group extravalues consists of (e) and counts only 1 row.

marked as duplicate by Paul White Apr 10 '16 at 10:07

This question has been asked before and already has an answer. If those answers do not fully address your question, please ask a new question.

2

Use the aggregate FILTER clause in Postgres 9.4+:

SELECT count(*) FILTER (WHERE rank = ANY ('{a,b,v}')) AS myvalues
     , count(*) FILTER (WHERE rank = ANY ('{c,d,j}')) AS yourvalues
     , count(*) FILTER (WHERE rank = 'e')             AS extravalues
FROM main;

Details and alternatives for older versions:

Not the answer you're looking for? Browse other questions tagged or ask your own question.