CODEWITHSUNDEEP
sqlpostgresql
John Smith
John Smith

Reputation: 203

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.

Upvotes: 0

Views: 440

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269673

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;

Upvotes: 3

Related Questions