SQL MAX of SUM without sub-querying

Question

I'm trying to execute a SQL query which requires grouping by MAX of SUM calculation (in PostgreSQL).

I found here some solutions which uses sub-querying but I need the solution without it (if it's possible).

Query:

SELECT "Festival".title,
       "Musician".aname, 
       SUM("Musician".salary * "Musician".percentage / 100) AS "agent_total_profit"
FROM "Festival"
INNER JOIN "Booked" 
    ON "Booked".title = "Festival".title
INNER JOIN "Musician"
    ON "Musician".id = "Booked".id
GROUP BY "Festival".title, 
         "Musician".aname
ORDER BY "Festival".title

Result:

the result is not as expected, I want to find for each festival title, the musician aname with the max agent_total_profit.

Desired result:

Thanks in advance.

for your requirement partition by will be useful see sample http://stackoverflow.com/questions/6198320/how-to-use-partition-by-or-max — recursion, Oct 28 '16 at 19:27

Gordon Linoff · Accepted Answer · 2016-10-28T20:14:41.407

Use DISTINCT ON:

SELECT DISTINCT ON (f.title) f.title, m.aname, 
       SUM(m.salary * m.percentage / 100) AS "agent_total_profit"
FROM "Festival" f INNER JOIN
     "Booked" b
     ON b.title = f.title INNER JOIN
     "Musician" m
    ON m.id = b.id
GROUP BY f.title, m.aname
ORDER BY f.title, "agent_total_profit" DESC;

The more traditional SQL method uses row_number():

SELECT f.*
FROM (SELECT f.title, m.aname, 
             SUM(m.salary * m.percentage / 100) AS "agent_total_profit",
             ROW_NUMBER() OVER (PARTITION BY f.title ORDER BY SUM(m.salary * m.percentage / 100) DESC) as seqnum
      FROM "Festival" f INNER JOIN
           "Booked" b
           ON b.title = f.title INNER JOIN
           "Musician" m
          ON m.id = b.id
      GROUP BY f.title, m.aname
     ) f
WHERE seqnum = 1;
ORDER BY f.title, "agent_total_profit" DESC;

SQL MAX of SUM without sub-querying

1 Answers1