mysql - 单个mysql查询

Question

我希望单个 mysql 查询添加一个新行作为总计，其中包含所有列值之和的值到以下查询。请帮助我

SELECT username,enddt,SUM(zerotothirty) AS 0To30,
SUM(thirtytosixty) AS 31To60,SUM(sixtytoonetwenty) AS 61To120,
SUM(onetwentytotwoforty) AS 121To240,SUM(greaterthantwoforty) AS '>240' 
FROM 
(SELECT username,DATE(enddt) AS enddt,tickid,
 IF(duration <=30, 1,0) zerotothirty,
 IF(duration BETWEEN 31 AND 60, 1,0) thirtytosixty,
 IF(duration BETWEEN 61 AND 120,1,0) sixtytoonetwenty,
 IF(duration BETWEEN 121 AND 240,1,0) onetwentytotwoforty,
 IF(duration >240,1,0) greaterthantwoforty
 FROM report)AS r
 GROUP BY username,DATE(enddt);

Answer 1

使用WITH ROLLUP GROUP BY 的修饰符:

GROUP BY username,DATE(enddt) WITH ROLLUP;

(在您的情况下，这将显示每个用户名的小计以及所有结果的总计)。