CODEWITHSUNDEEP
mysqlcountsumrows
sona das
sona das

Reputation: 1439

Mysql Count and Sum Rows

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 |
 +----------+---------+---------+

Upvotes: 1

Views: 76

Answers (1)

Dylan Su
Dylan Su

Reputation: 6065

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;

Upvotes: 2

Related Questions