1

I am having a mysql query (created by sagi) :-

 SELECT       'blr' AS Location,       COUNT(*) AS playCnt,       (select count(*) from blr_skip WHERE blr_skip_dt like '%2016-03-20%') as skipCnt FROM  blr_play  WHERE blr_play_dt like '%2016-03-20%' UNION SELECT       'chen' AS Location,       COUNT(*) AS playCnt,       (select count(*) from chen_skip WHERE chen_skip_dt like '%2016-03-20%') as skipCnt FROM  chen_play  WHERE chen_play_dt like '%2016-03-20%';

which gives output like this:-

 +----------+---------+---------+
 | Location | playCnt | skipCnt |
 +----------+---------+---------+
 | blr      |      25 |       8 |
 | chen     |      12 |       1 |
 +----------+---------+---------+

I want to add one more row of total at the end of the column like this :-

 +----------+---------+---------+
 | Location | playCnt | skipCnt |
 +----------+---------+---------+
 | blr      |      25 |       8 |
 | chen     |      12 |       1 |
 +----------+---------+---------+
 |Total     |      37 |       9 |
 +----------+---------+---------+
sona das
  • 1,299
  • 3
  • 9
  • 10

1 Answers1

2

Try this:

SELECT
    IFNULL(Location, 'Total') AS Location,
    SUM(playCnt) AS playCnt,
    SUM(skipCnt) AS playCnt
FROM
(
    SELECT       'blr' AS Location,       COUNT(*) AS playCnt,       (select count(*) from blr_skip WHERE blr_skip_dt like '%2016-03-20%') as skipCnt 
    FROM  blr_play  
    WHERE blr_play_dt like '%2016-03-20%' 
    UNION 
    SELECT       'chen' AS Location,       COUNT(*) AS playCnt,       (select count(*) from chen_skip WHERE chen_skip_dt like '%2016-03-20%') as skipCnt 
    FROM  chen_play  
    WHERE chen_play_dt like '%2016-03-20%'
) t1
GROUP BY Location WITH ROLLUP;
Dylan Su
  • 5,975
  • 1
  • 16
  • 25
  • i m not getting a proper total. If you want the mysql dump file then here is the link :- https://www.mediafire.com/?444n3tvcvj24dpm – sona das Mar 23 '16 at 08:05
  • ok just made a small change in ur query it worked like charm added sum(playcnt), sum (skipcnt) – sona das Mar 23 '16 at 08:08