CODEWITHSUNDEEP
sqlpostgresqlaggregate
Maarten van Middendorp
Maarten van Middendorp

Reputation: 11

select value based on max of other column

I have a few questions about a table I'm trying to make in Postgres. The following table is my input:

id area count function
1 100 20 living
1 200 30 industry
2 400 10 living
2 400 10 industry
2 400 20 education
3 150 1 industry
3 150 1 education

I want to group by id and get the dominant function based on max area. With summing up the rows for area and count. When area is equal it should be based on max count, when area and count is equal it should be based on prior function (i still have to decide if education is prior to industry or vice versa). So the result should be:

id area count function
1 300 50 industry
2 1200 40 education
3 300 2 industry

I tried a lot of things and maybe it's easy, but i don't get it. Can someone help to get the right SQL?

Upvotes: 1

Views: 1261

Answers (4)

Lajos Arpad
Lajos Arpad

Reputation: 76551

This is how you get the function for each group based on id:

select id, function
from yourtable yt1
left join yourtable yt2
on yt1.id = yt2.id and yt1.area < yt2.area
where yt2.area.id is null;

(we ensure that no yt2 exists that would be of the same id but of higher areay)

This would work nicely, but you might have several max areas with different values. To cope with this isue, let's ensure that exactly one is chosen:

select id, max(function) as function
from yourtable yt1
left join yourtable yt2
on yt1.id = yt2.id and yt1.area < yt2.area
where yt2.area.id is null
group by id;

Now, let's join this to our main table;

select yourtable.id, sum(yourtable.area), sum(yourtable.count), t.function
from yourtable
join (
    select id, max(function) as function
    from yourtable yt1
    left join yourtable yt2
    on yt1.id = yt2.id and yt1.area < yt2.area
    where yt2.area.id is null
    group by id
) t
on yourtable.id = t.id
group by yourtable.id;

Upvotes: 0

Stefanov.sm
Stefanov.sm

Reputation: 13049

Use a scalar sub-query for "function".

select t.id, sum(t.area), sum(t.count), 
(
  select "function"
  from the_table
  where id = t.id 
  order by area desc, count desc, "function" desc
  limit 1
)  as "function"
from the_table as t
group by t.id order by t.id;

SQL Fiddle

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269873

One method uses row_number() and conditional aggregation:

select id, sum(area), sum(count),
       max(function) over (filter where seqnum = 1) as function
from (select t.*,
             row_number() over (partition by id order by area desc) as seqnum
      from t
     ) t
group by id;

Another method uses ``distinct on`:

select id, sum(area) over (partition by id) as area,
       sum(count) over (partition by id) as count,
       function
from t
order by id, area desc;

Upvotes: 1

FatFreddy
FatFreddy

Reputation: 1210

you can use sum as window function:

select distinct on (t.id)
id,
sum(area) over (partition by id) as area,
sum(count) over (partition by id) as count,
( select function from tbl_test where tbl_test.id = t.id order by count desc limit 1 ) as function 

from tbl_test t

Upvotes: 0

Related Questions