Question 113932 in PostgreSQL Question posted in
The official documentation can be found here.

Postgresql – SQL select the max from each group and given them different lables

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.

Tags:

2

Answers


  1. 0

    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

    Also, as you can see, I have changed the name of the column count to count_ab because it is not a good practice to use keywords as columns names.

    Login or Signup to reply.
  2. 0

    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|           |
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search