CODEWITHSUNDEEP
mysqlsqlmaxaggregate-functions
tonix
tonix

Reputation: 6939

Better query to select the max value of an aggregation with a count result and retain another column MySQL?

Hi everyone I have a table which stores 'visits' of users on a museum. Every record is a visit of a visitor on a particular day and it has a column which stores the number of minors that came to the museum with the visitor (i.e if I have a number of 3 minors it means that that visit was made by 4 people, the visitor and 3 minors).

I need to select the day where there was the max number of visitors (minors included) and I have made a query like this:

SELECT * FROM (
    SELECT (COUNT(*) + SUM(visita_numero_minorenni)) as number_of_visitors, visit_date
    FROM visite 
    WHERE visit_date BETWEEN '2014-06-13' AND '2014-06-20'
    GROUP BY visit_date
) as b 
WHERE number_of_visitors IN (
    SELECT MAX(number_of_visitors) as number_of_visitors
    FROM (
        SELECT (COUNT(*) + SUM(number_of_minors)) as number_of_visitors, visit_date
        FROM visite 
        WHERE visit_date BETWEEN '2014-06-13' AND '2014-06-20'
        GROUP BY visit_date
    ) as c
)

Let me explain how it works quickly: The SELECT (COUNT(*) + SUM(number_of_minors).... sub-query recovers the total number of visitors per day in the specified range (e.g. BETWEEN '2014-06-13' AND '2014-06-20'), an example of the output is:

number_of_visitors       date
    58                   2014-06-14
    48                   2014-06-15
    41                   2014-06-16
    20                   2014-06-17
    138                  2014-06-18
    9                    2014-06-19

Then I simply need to recover the penultimate record of this sub-query (cause the max number of visitors in a day is 138, in the date 2014-06-18). So I use a WHERE clause with an IN which now takes a sub-query with the MAX aggregate function and returns the expected result:

number_of_visitors       date
138                      2014-06-18

It works but I think it is not efficient to run the COUNT(*) + SUM(... sub-query twice, what do you think? Can I improve this query?

Upvotes: 0

Views: 239

Answers (2)

Sefer K
Sefer K

Reputation: 514

SELECT  visit_date, SUM(visita_numero_minorenni) as number_of_visitors,
        FROM visite  
        WHERE visit_date BETWEEN '2014-06-13' AND '2014-06-20'
        GROUP BY visit_date
order by SUM(visita_numero_minorenni) desc
LIMIT 1

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1270181

I think you can get what you want as:

    SELECT (COUNT(*) + SUM(number_of_minors)) as number_of_visitors, visit_date
    FROM visite 
    WHERE visit_date BETWEEN '2014-06-13' AND '2014-06-20'
    GROUP BY visit_date
    ORDER BY number_of_visitors DESC
    LIMIT 1;

This orders the result set by the number of visitors, with the biggest number first. Then it takes the first row.

One way this differs from your query is that this returns only one row. Your version would return all days with the maximum, if there are multiple days with the same number of visitors.

EDIT:

As a note, you can get all such days with a subquery and group_concat():

SELECT number_of_visitors, group_concat(visit_date) as visit_dates
FROM (SELECT (COUNT(*) + SUM(number_of_minors)) as number_of_visitors, visit_date
      FROM visite 
      WHERE visit_date BETWEEN '2014-06-13' AND '2014-06-20'
      GROUP BY visit_date
     ) t
GROUP BY number_of_visitors
ORDER BY number_of_visitors DESC
LIMIT 1;

Upvotes: 2

Related Questions