SQL select the max from each group and given them different lables-CodePudding

For the following tables:

-- People
 id | category | count
---- ---------- -------
  1 | a        |     2
  1 | a        |     3
  1 | b        |     2
  2 | a        |     2
  2 | b        |     3
  3 | a        |     1
  3 | a        |     2

I know that I can find the max count for each id in each category by doing:

SELECT id, category, max(count) from People group by category, id;

With result:

 id | category | max
---- ---------- -------
  1 | a        |     3
  1 | b        |     2
  2 | a        |     2
  2 | b        |     3
  3 | a        |     2

But what if now I want to label the max values differently, like:

 id | max_b_count | max_a_count
---- ------------- ------------
  1 | 2           |     3
  2 | 3           |     2
  3 | Null        |     2

Should I do something like the following?

WITH t AS (SELECT id, category, max(count) from People group by category, id)
SELECT t.id, t.count as max_a_count from t where t.category = 'a'
FULL OUTER JOIN t.id, t.count as max_b_count from t where t.category = 'b'
on t.id;

It looks weird to me.

CodePudding user response：

This is one way you can do it:

with T as (select id, category, max(count_ab) maks
from people
group by id, category
order by id)
select t3.id
       , (select t1.maks from T t1 where category = 'b' and t1.id = t3.id) max_b_count 
       , (select t2.maks from T t2 where category = 'a' and t2.id = t3.id) max_a_count 
from T t3
group by t3.id
order by t3.id

Here is a demo

CodePudding user response：

This is the exact use case why the filter_clause was added to the Aggregate Expressions

With filter_clause you may limit which row you aggregate

aggregate_name ( * ) [ FILTER ( WHERE filter_clause ) ]

Your example

SELECT id, 
max(count)  filter (where category = 'a') as max_a_count,
max(count)  filter (where category = 'b') as max_b_count
from People
group by id
order by 1;

id|max_a_count|max_b_count|
-- ----------- ----------- 
 1|          3|          2|
 2|          2|          3|
 3|          2|           |