取每天的最大值并总结mysql

Question

I have a table with 3 columns (server varchar(20), transactions int(20), timestamp timestamp(0)). There are multiple servers that log the transactions they get hit with over the course of a day. I want to write a query that sums the max transaction (per day) over the course of a multiple months and display a column for each month.

I believe I am close with the following broken query, but the problem is that it takes the max value of the whole month rather than getting the sum of the max of each day in the month.

SELECT IFNULL(server, 'TOTAL') AS Server,
       FORMAT(SUM(MAX(CASE WHEN DATE(TIMESTAMP) >= DATE('2014-10-01')
                  AND DATE(TIMESTAMP) < DATE('2014-11-01') THEN transactions ELSE 0 END),0)) AS 'Oct - 2014',
       FORMAT(SUM(MAX(CASE WHEN DATE(TIMESTAMP) >= DATE('2014-11-01')
                  AND DATE(TIMESTAMP) < DATE('2014-12-01') THEN transactions ELSE 0 END),0)) AS 'Nov - 2014',
       FORMAT(SUM(MAX(CASE WHEN DATE(TIMESTAMP) >= DATE('2014-12-01')
                  AND DATE(TIMESTAMP) < DATE('2015-01-01') THEN transactions ELSE 0 END),0)) AS 'Dec - 2014',
       '' AS 'Total'
FROM transactionstable
GROUP BY Server WITH ROLLUP;

Answer 1

You want nested group bys, first by date and server to get the maximum and then by server to get the sum(). A sketch of the query is:

select server, sum(maxt)
from (select date(timestamp) as d, server, max(transactions) as maxt
      from transactiontable tt
      where date(timestamp) between @TIMESTAMP1 and @TIMESTAMP2
      group by date(timestamp), server
     ) t
group by server;

EDIT:

Just do a conditional aggregation in the outer query:

select server, sum(maxt),
       sum(case when year(timestamp) = 2014 and month(timestamp) = 11 then maxt end) as sum_201411,
       sum(case when year(timestamp) = 2014 and month(timestamp) = 12 then maxt end) as sum_201412,
       sum(case when year(timestamp) = 2015 and month(timestamp) = 1 then maxt end) as sum_201501
from (select date(timestamp) as d, server, max(transactions) as maxt
      from transactiontable tt
      where date(timestamp) between @TIMESTAMP1 and @TIMESTAMP2
      group by date(timestamp), server
     ) t
group by server;