Mysql sum for two dates for different and missing and new

Question

The sum of the Mysql TOTAL request by two dates for different and missing and new GROUP BY NAME

10/10/2016  xx  10
10/10/2016  xx  15
10/10/2016  yy  15
10/10/2016  yy  45
10/10/2016  zz  25
10/10/2016  zz  5
10/10/2016  xx  5
10/11/2016  xx  15
10/11/2016  zz  25
10/11/2016  zz  5
10/11/2016  xx  45
10/11/2016  aa  45

I need this result

name    10/10/2016  10/11/2016  DIFF
+-----+-----+----+-----+-----+-----+
aa      NULL        45          45
xx      30          60          30
yy      60          NULL        -60
zz      30          30          0

Table

CREATE TABLE IF NOT EXISTS `test` (`date` date NOT NULL,`name` varchar(10) NOT NULL,`total` int(10) NOT NULL) ENGINE=MyISAM DEFAULT CHARSET=latin1;

INSERT INTO `test` (`date`, `name`, `total`) VALUES
('2016-10-10', 'xx', 10),('2016-10-10', 'xx', 15),('2016-10-10', 'yy', 15),('2016-10-10', 'yy', 45),('2016-10-10', 'zz', 25),('2016-10-10', 'zz', 5),
('2016-10-10', 'xx', 5),('2016-10-11', 'xx', 15),('2016-10-11', 'zz', 25),('2016-10-11', 'zz', 5),('2016-10-11', 'xx', 45),('2016-10-11', 'aa', 45);

Answer 1

Assuming you only have two dates in the request, you should be able to make a regular summary request here. There is one trick below. In the expected output, you want it to NULLappear if the given nameone has no entries for the given date. But in the difference column, you want to treat this entry as zero. I implemented this by adding a condition ELSEthat uses zero when the given name / date is missing.

SELECT name,
       SUM(CASE WHEN date = '2016-10-10' THEN total END) AS 10_10_2016,
       SUM(CASE WHEN date = '2016-10-11' THEN total END) AS 10_11_2016,
       SUM(CASE WHEN date = '2016-10-11' THEN total ELSE 0 END) -
       SUM(CASE WHEN date = '2016-10-10' THEN total ELSE 0 END) AS DIFF
FROM test
GROUP BY name

Answer 2

SELECT *,IFNULL(data1,0)-IFNULL(data2,0) FROM(
SELECT  `date`,`name`,
        MAX(IF(`date` = '2016-10-10',tot, NULL)) data1,
        MAX(IF(`date` ='2016-10-11',tot, NULL)) data2
FROM    (SELECT `date`,`name`,SUM(total) AS tot FROM test GROUP BY `name`,`date`) AS tab
GROUP   BY `name`)  AS t