0

I'm not quite sure how to phrase this question, which is part of the reason I am having problems producing or finding an answer.

Given the following table:

id class region_id rank irrelevant_column_1_of_10
1 class1 A over irrelevant value
2 class1 A over
3 class1 A under
4 class1 B over
5 class1 B over
6 class1 C under

How can the following report be produced for each row where class=class1

(region_id=A, over_count: 2, under_count: 1),
(region_id=B, over_count: 2, under_count: 0),
(region_id=C, over_count: 0, under_count: 1),

My SQL is basic. I tried GROUP BY, but can't get it do more than:

(A, over, 2),
(A, under, 1),
(B, over, 2),
(B, under, 0),
(C, over, 0),
(C, under, 1)

2 Answers 2

2

You can use a filtered aggregation:

select region_id, 
       count(*) filter (where rank = 'over') as over_count,
       count(*) filter (where rank = 'under') as under_count
from the_table
where class = 'class1'
group by region_id
0
1

A method that I've used a fair bit in MS SQL Server which I think will work directly in postgres is to SUM based on CASE expressions:

  SELECT region_id
       , SUM(CASE WHEN rank = 'over'  THEN 1 ELSE 0 END) AS over_count
       , SUM(CASE WHEN rank = 'under' THEN 1 ELSE 0 END) AS under_count
    FROM your_table
GROUP BY region_id

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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