CODEWITHSUNDEEP
mysqldatetimeaggregate-functions
Alan
Alan

Reputation: 25

How to sum the max values in a query e.g. sum(max(value))

I am trying to grab each max count for each day in a month. Then I want to sum the max values for the month.

        $result = mysql_query(
                          "
                          SELECT DATE_FORMAT(orderDate,'%M %e, %Y') orderDate, MAX(Count) AS maxCount
                          FROM 
                                (
                                SELECT DATE_FORMAT(orderDate,'%M %e, %Y') orderDate, SUM(Count) AS sumCount
                                FROM testLocation
                                WHERE orderDate >= '$data1' AND orderDate <  '$data2' + INTERVAL 1 DAY
                                )
                          GROUP BY MONTH(orderDate)
                          "
                          ,$link
                          );

The way I have the count is that it increments throughout the day. So for example I have a Count of 500 at time 5/5/2016 08:00:00 but at the end of the day 5/5/2016 23:59:59 the count is 800. I only want to grab the 800 number and not add the 500 to the 800.

I want to take the max value of each day of a given month. Then I want to sum all the max values to create a summary for the month.

Upvotes: 0

Views: 85

Answers (1)

O. Jones
O. Jones

Reputation: 108641

I guess you are trying to summarize your testLocation table by month. It's hard to guess from your query what you are trying to do, exactly.

Edit:

First, you need a daily rollup, to get the highest Count value.

             SELECT DATE(orderDate) AS day, 
                    MAX(Count)      AS dayCount
               FROM testLocation
           GROUP BY DATE(orderDate)

That gets you a row for each day, with the highest value of Count for that day.

Then you need a monthly rollup to add up the rows of the daily rollup. To do this you use the daily rollup as a virtual table.

 SELECT LAST_DAY(day) monthEnding,
        SUM(dayCount) AS monthSum
   FROM (
             SELECT DATE(orderDate) AS day, 
                    MAX(Count)      AS dayCount
               FROM testLocation
           GROUP BY DATE(orderDate)
        ) m
  GROUP BY LAST_DAY(day)

This gets you what you want. The LAST_DAY() function here is convenient: it takes any DATETIME or DATE value and converts it to the last day of the month.

Upvotes: 0

Related Questions