CODEWITHSUNDEEP
postgresql
The Wolf
The Wolf

Reputation: 57

How to get the MAX(SUM of values) to find the category with the biggest total? PostgreSQL

I have two tables. One is Transactions and the other is Tickets. In Tickets I have the Ticket_Number,the name of the Category(Theater,Cinema,Concert), the Price of the Ticket. In Transactions I also have the Ticket_Number. What i want to do is to Get a SUM of money for each Category, and then with that data I want to Select the Category with the most money.

I already managed to get the SUM for each category but I am stuck here

SELECT category, SUM (Tickets.Price) AS Price  
FROM Tickets,Transactions  
WHERE Tickets.ticket_num=Transactions.ticket_num  
GROUP BY Category  
ORDER BY Price DESC;

I know i can add LIMIT 1 but I know it's not correct because 2 or more values can be the same

Upvotes: 0

Views: 230

Answers (3)

Kamil Gosciminski
Kamil Gosciminski

Reputation: 17147

What you are looking for is a window function DENSE_RANK() which will handle ties properly.

RANK() will also work for your case, but if you would like to extend it to get TOP N places with ties (where N > 1), dense rank is the way to go.

SELECT Category, Price
FROM (
  SELECT 
    Category, 
    SUM(ti.Price) AS Price, 
    DENSE_RANK() OVER (ORDER BY SUM(ti.Price) DESC) AS rnk
  FROM Tickets ti
  INNER JOIN Transactions tr ON
    ti.ticket_num = tr.ticket_num  
  GROUP BY Category
  ) t
WHERE rnk = 1

I've also replaced the old style and not recommended joining of tables as comma separated list in FROM clause to a proper INNER JOIN clause and assigned aliases to tables.

Upvotes: 1

sticky bit
sticky bit

Reputation: 37472

You can use rank() to rank the sums of the prices, more expensive first.

SELECT category,
       price
       FROM (SELECT category,
                    sum(tickets.price) price,
                    rank() OVER (ORDER BY sum(tickets.price) DESC) r
                    FROM tickets
                         INNER JOIN transactions  
                                    ON transactions.ticket_num = tickets.ticket_num
                    GROUP BY category) x
       WHERE r = 1;

I also took the liberty to rewrite your join from the ancient comma style to a modern, clearer version.

Upvotes: 0

Tim Biegeleisen
Tim Biegeleisen

Reputation: 521093

Using ROW_NUMBER to generate a sequence based on the sum of the price. Then, restrict to only the matching aggregated row with the highest total price.

WITH cte AS (
    SELECT category, SUM(t1.Price) AS Price,
        ROW_NUMBER() OVER (ORDER BY SUM(t1.Price) DESC) rn  
    FROM Tickets t1
    INNER JOIN Transactions t2
        ON t1.ticket_num = t2.ticket_num  
    GROUP BY Category
)

SELECT category, Price
FROM cte
WHERE rn = 1
ORDER BY Price DESC;

Note that if you want to capture all categories tied for the highest price, should a tie occur, then replace ROW_NUMBER in the above CTE with RANK, keeping everything else the same.

Upvotes: 1

Related Questions