Select rows and group by value without other columns in aggregate function

Question

I try to get some rows from a table where I group by one value but the other columns I want to get back are not in the group by or any aggregate clause. It works fine on sqlite but does not work on postgresql.

value1	created	value2	name
2	2022-01-01	10	foo
5	2022-03-01	4	foo
1	2022-01-01	6	bar
7	2022-02-01	3	bar
2	2022-04-01	20	bar

I would all rows grouped by name where created is max for that name. E.g:

MAX(value1)	created	value2	SUM(value)	name
5	2022-03-01	4	14	foo
7	2022-04-01	20	29	bar

The problem is that I cannot use MAX in a where clause, nor can I just group by name as SQL complains that I need to group by value2 as well, which I don't want. I want just the value from the row with the max value1 grouped by name.

Or in other words: I want one row per name, which is selected based on the max value1 with an additional column that is the sum of all value2 for that name.

The closest I got is:

SELECT MAX(value1), created, value2, SUM(value2), name
FROM table
GROUP BY name

But obviously that does not work. Any help is welcome!

@secana Still need to fix your value2 in your update, should be 3 not 20 — Ryan Wilson
– Ryan Wilson, Commented Aug 2, 2022 at 20:27

J Spratt · Accepted Answer · 2022-08-02 20:04:48Z

1

Might be a couple options here but you can do this with a CTE like the following:

WITH agg_data AS
(
SELECT name
     , MAX(value1) maxval1
     , SUM(value2) sumval2
  FROM t
 GROUP BY name
)
SELECT ad.*
     , t2.created
     , t2.value2
  FROM agg_data ad
  JOIN t t2 ON t2.value1 = ad.maxval1 AND t2.name = ad.name
;

Here is a DBFiddle to show a working example.

answered Aug 2, 2022 at 20:04

J Spratt

2,0521 gold badge16 silver badges24 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

shawnt00 · Accepted Answer · 2022-08-02 20:37:00Z

1

with data as (
    select *, row_number() over (partition by name order by created desc) rn
    from T
)
select max(created) as created, max(value2) filter (where rn = 1) as value2, name
from data
group by name;

You could also use this inside the CTE:

first_value(value2) over (partition by name order by created desc) as value2

edited Aug 2, 2022 at 20:37

answered Aug 2, 2022 at 20:29

shawnt00

18.1k3 gold badges23 silver badges25 bronze badges

Comments

Ryan Wilson · Accepted Answer · 2022-08-02 20:13:09Z

0

You could use a subquery:

SELECT m.maxvalue1, t.created, t.value2, m.sumvalue2, t.name
FROM table t
inner join
(
   SELECT MAX(value1) as maxvalue1, SUM(value2) as sumvalue2, name
   FROM table
   GROUP BY name
) m
on m.value1 = t.value1 and m.name = t.name

edited Aug 2, 2022 at 20:13

answered Aug 2, 2022 at 20:05

Ryan Wilson

10.9k3 gold badges23 silver badges46 bronze badges

Collectives™ on Stack Overflow

Select rows and group by value without other columns in aggregate function

3 Answers 3

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related