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:
(a,b,v)should come in one group by name
(c,d,j)should come in another group by name
(e)should come to another group by name
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.