Ashok Ashok - 10 months ago 37
PHP Question

Mysql sum by two date by different and missing and new

Mysql query sum TOTAL by two dates by 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 want 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 Source

Assuming you only have two dates in the query, you should be able to do a regular pivot query here. There is one trick below. In your expected output you want NULL to appear if a given name has no entries for a given date. But in the difference column you want to treat that entry as being zero. I implemented this by adding an ELSE condition which uses zero when a given name/date be absent.

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