CODEWITHSUNDEEP
mysqlsqldatabase
Kiran Baktha
Kiran Baktha

Reputation: 667

MySQL syntax query on aggregation

I have a table called TICKET with columns FLIGHTNUMBER and INSURED. INSURED has only 0 or 1 values. FLIGHTNUMBER has duplicates. I wanted to get the FLIGHTNUMBER with the max number of 1s in INSURED. Here's what I tried:

SELECT FLIGHTNUMBER 
FROM (SELECT FLIGHTNUMBER,SUM(INSURED) AS INSURANCE_COUNT 
      FROM TICKET 
      GROUP BY TICKET.FLIGHTNUMBER) AS OUT2 
WHERE OUT2.INSURANCE_COUNT = MAX(INSURANCE_COUNT) ;

AND 

SELECT FLIGHTNUMBER 
FROM (SELECT FLIGHTNUMBER,SUM(INSURED) AS INSURANCE_COUNT 
      FROM TICKET 
      GROUP BY TICKET.FLIGHTNUMBER 
      HAVING INSURANCE_COUNT = MAX(SUM(INSURED))) AS OUT2;

But both are causing errors. Could you please tell me the correct syntax. Thanks in advance.

Upvotes: 1

Views: 47

Answers (2)

Barmar
Barmar

Reputation: 782785

The first one doesn't work because you can't use an aggregation function in a WHERE clause. Aggregation doesn't happen until after all the rows are selected, and WHERE is used to select the rows, so there's a chicken-and-egg problem.

The second doesn't work because you can't nest aggregation functions.

You don't need to calculate MAX(INSURANCE_COUNT). Just order the query and take the first one.

SELECT flightnumber
FROM (SELECT flightnumber, SUM(INSURED) AS insurance_count
      FROM ticket
      GROUP BY flightnumber) AS out2
ORDER BY insurance_count DESC
LIMIT 1

You don't even need insurance_count in the SELECT list, so you don't need to wrap it in a subquery to get rid of it.

SELECT flightnumber
FROM ticket
GROUP BY flightnumber
ORDER BY SUM(insured) DESC
LIMIT 1

However, if there are multiple flights that are tied for the max, this will just return one of them. If you need to get all of them, you need to write a subquery that gets the max, and join with that.

SELECT flightnumber
FROM (SELECT flightnumber, SUM(insured) AS insurance_count
      FROM ticket
      GROUP BY flightnumber) AS out2
JOIN (SELECT SUM(insured) AS max_insurance_count
      FROM ticket
      GROUP BY flightnumber
      ORDER BY max_insurance_count DESC
      LIMIT 1) AS out3
ON out2.insurance_count = out3.max_insurance_count

Upvotes: 1

Jeff Standen
Jeff Standen

Reputation: 6886

You can do:

SELECT flightnumber, SUM(insured) AS insured_count 
FROM ticket 
GROUP BY flightnumber 
ORDER BY insured_count DESC 
LIMIT 1;

This is just a SUM aggregation on the insured column, grouped by flightnumber, and sorted on the insured_count in descending order. We only keep the first result.

This doesn't consider what would happen if two flightnumbers had the same max insured count. You could break ties with a second ORDER BY field, or run another outer query.

If you had a flightnumber table you could just update its insured_count field with a SUM() every time you modify the ticket table. That would be more efficient if you're running this report very often, the ticket table ends up with a lot of rows, and most of the rows aren't changing after a flight is complete.

Upvotes: 1

Related Questions