John Smith John Smith - 1 month ago 17
SQL Question

SQL MAX of SUM without sub-querying

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:

unwanted result of the query above

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:

enter image description here

Thanks in advance.

Answer

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;