How to show ONLY the max value of a inner join table column?

Question

I used INNER JOIN on two tables :

Transactions 
 - transaction_id (PK)
 -ticket_id (FK) reference to ticketsforsale

Ticketsforsale : 
 - ticket_id (PK)
 - type
 - price 

(there are more columns in each table but serve no purpose for this question)

The query i tried is the following :

SELECT ticketsforsale.type , SUM(ticketsforsale.price) AS TotalProfit
   FROM ticketsforsale INNER JOIN transactions 
   ON ticketsforsale.ticket_id = transactions.ticket_id 
GROUP BY ticketsforsale.type 

The result is : 
  Sports | 300
  Cruise | 600
  Theater| 100

I tried using this line in the query

WHERE TotalProfit = SELECT(MAX(TotalProfit)

But I can't figure out the right place for this line.

What i want the query to do is to show only the ROW containing the max value of "TotalProfit" . I am just missing the right MAX function usage on this query , thanks !

Answer 1

Use ORDER BY and a limit the result set to one row:

SELECT tfs.type , SUM(tfs.price) AS TotalProfit
FROM ticketsforsale tfs INNER JOIN
     transactions t 
     ON tfs.ticket_id = t.ticket_id 
GROUP BY tfs.type 
ORDER BY TotalProfit DESC
FETCH FIRST 1 ROW ONLY;

Note that I introduced table aliases as well, so the query is easier to write and to read.

Based on this query, you don't seem to need the JOIN:

SELECT tfs.type , SUM(tfs.price) AS TotalProfit
FROM ticketsforsale tfs 
GROUP BY tfs.type 
ORDER BY TotalProfit DESC
FETCH FIRST 1 ROW ONLY;