Ashok Ashok - 1 month ago 6
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

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
Comments