Get row with greatest value in group plus sum for the group

Question

Basically, I have a table that looks like this:

Fruit | Juiciness | Brand | Price
---------------------------------
Apple |     2     |   A   | 0.50
Pear  |     1     |   A   | 0.70
Plum  |     6     |   A   | 0.40
Kiwi  |     5     |   B   | 0.80
Grape |     4     |   B   | 0.01

I'm looking to output two rows, one for brand A and one for brand B, with the name of the juiciest fruit (max juiciness value), and the sum price of all fruits from that brand. So:

Fruit | Brand | Price
---------------------------------
Plum  |   A   | 1.60
Kiwi  |   B   | 0.81

I was thinking of breaking it up into a few different CTE/subqueries, one with a simple SUM(Price) and one with a DISTINCT ON and ORDER BY to get the juiciest fruit and then joining them back together. But somehow even that wasn't working for me. (Something about me not wanting to display the juiciness seems to have been tripping Postgres up, but I'm not sure.)

So do you have your answer? – Erwin Brandstetter Apr 28 '21 at 12:20 — Erwin Brandstetter, Apr 28 '21 at 12:20

score 1 · Answer 1 · edited Mar 11 '21 at 03:49

You want two aggregates: a sum and a maximum, and both times you want to group by brand. The challenging part is that you want the fruit name as well (otherwise it would be easy).

So first add the sum with a window function, then for each group fetch only the juiciest fruit:

SELECT DISTINCT ON (brand)
       fruit,
       brand,
       sum AS price
FROM (SELECT fruit,
             juiciness,
             brand,
             sum(price) OVER (PARTITION BY brand) AS sum
      FROM mytable) AS subq
ORDER BY brand, juiciness DESC;

With window functions, you often end up with subqueries.

score 1 · Answer 2 · answered Mar 11 '21 at 03:42

Since DISTINCT or DISTINCT ON are applied after window functions, you can do this without subquery:

SELECT DISTINCT ON (brand)
       fruit, brand, sum(price) OVER (PARTITION BY brand) AS sum_price
FROM   tbl
ORDER  BY brand, juiciness DESC;

db<>fiddle here

Get row with greatest value in group plus sum for the group

2 Answers2