mysql with rollup not working properly
SELECT ifnull(username,'Total'),COUNT(DISTINCT tickid) AS NewTick,COUNT(tickid)-COUNT(DISTINCT tickid) AS ReopenedTick,
      SUM( IF(duration <=30, 1,0 )) AS 0To30,
      SUM( IF(duration BETWEEN 31 AND 60, 1,0 )) AS 31To60,
      SUM( IF(duration BETWEEN 61 AND 120,1,0 )) AS 61To120,
      SUM( IF(duration BETWEEN 121 AND 240,1,0 )) AS 121To240,
      SUM( IF(duration >240,1,0)) AS '>240'
   FROM report WHERE username IS NOT NULL AND deptid=1
   GROUP BY username WITH ROLLUP;


                This query produces the following output.

username	NewTick	ReopenedTick	0To30	31To60	61To120	121To240	>240
prabhakaran	11	1	         8	2	1	    0	          1
Sarojini	21	9	         24	6	0	    0	          0
sonal	        3	0	         2	1	0	    0	          0
vinoth	        2	0	         2	0	0	    0	          0
[Internal]	13	0	         6	0	1	    6	          0
Total           49	11	         42	9	2	    6	          1

Total of newticket and reopened tickets are not correct. Please help me. Thanks in advance.





	

mysql with rollup not working properly
===

SELECT ifnull(username,'Total'),COUNT(DISTINCT tickid) AS NewTick,COUNT(tickid)-COUNT(DISTINCT tickid) AS ReopenedTick,
      SUM( IF(duration <=30, 1,0 )) AS 0To30,
      SUM( IF(duration BETWEEN 31 AND 60, 1,0 )) AS 31To60,
      SUM( IF(duration BETWEEN 61 AND 120,1,0 )) AS 61To120,
      SUM( IF(duration BETWEEN 121 AND 240,1,0 )) AS 121To240,
      SUM( IF(duration >240,1,0)) AS '>240'
   FROM report WHERE username IS NOT NULL AND deptid=1
   GROUP BY username WITH ROLLUP;


                This query produces the following output.

username	NewTick	ReopenedTick	0To30	31To60	61To120	121To240	>240
prabhakaran	11	1	         8	2	1	    0	          1
Sarojini	21	9	         24	6	0	    0	          0
sonal	        3	0	         2	1	0	    0	          0
vinoth	        2	0	         2	0	0	    0	          0
[Internal]	13	0	         6	0	1	    6	          0
Total           49	11	         42	9	2	    6	          1

Total of newticket and reopened tickets are not correct. Please help me. Thanks in advance.





	
