CODEWITHSUNDEEP
mysql
Jamie T
Jamie T

Reputation: 7

Left Join not working with conditions

I have two counters, one for units, one for waste. I need to pull the sum of the timers and separate by Year.
The Left Join kinda works to get the combined, but as soon as I put a where condition to separate the timers, the think falls apart.
Could someone please check this out and give point me in the right direction?
Thanks. Jamie.

 SELECT year(counts.ts) as SalesYear, Q1.Res1 AS units, Q2.Res2 as rate
 FROM 
 (counts
 LEFT JOIN (SELECT (counts.ts), SUM(counts.count) AS Res1 
 FROM counts where counts.`name` = 104 and counts.count > 0 ) AS Q1 ON (counts.ts) = Q1.ts) 

 LEFT JOIN (SELECT (counts.ts), Sum(counts.count) AS Res2
 FROM counts where counts.`name` = 107 and counts.count > 0) AS Q2 ON (counts.ts) = Q2.ts

 GROUP BY year(counts.ts)
 order by year(counts.ts)

Upvotes: 0

Views: 60

Answers (1)

Paul Maxwell
Paul Maxwell

Reputation: 35603

I believe you can just use "conditional aggregates" like this:

SELECT
      YEAR(counts.ts) as SalesYear
    , SUM(case when counts.count = 104 then counts.count else 0 end) AS Res1 
    , SUM(case when counts.count = 107 then counts.count else 0 end) AS Res2
FROM counts 
WHERE counts.name IN (104,107)
      AND counts.count > 0 
GROUP BY YEAR(counts.ts) 
ORDER BY YEAR(counts.ts)
;

As sample data has now been provided via a sqlfiddle instance, the following is now relevant. Note that the query above, like the query is the question, the conditions were incorrectly based on the column counts.count instead of counts.name. Once that is corrected the exact same query structure provides a result. (Please take note that I could not have known this without seeing the data.)

SQL Fiddle

MySQL 5.6 Schema Setup:

DROP TABLE IF EXISTS `counts`;
CREATE TABLE `counts` (
  `id` int(15) NOT NULL AUTO_INCREMENT,
  `name` varchar(16) DEFAULT NULL,
  `ts` datetime DEFAULT NULL,
  `count` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `UniqueDataIndex` (`name`,`ts`,`count`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=475152 DEFAULT CHARSET=utf8;

INSERT INTO `counts` VALUES ('475026', '107             ', '2015-01-27 10:18:00', '40');
INSERT INTO `counts` VALUES ('475032', '104             ', '2015-01-27 10:20:00', '11');
INSERT INTO `counts` VALUES ('475039', '107             ', '2015-01-27 10:22:00', '109');
INSERT INTO `counts` VALUES ('475046', '104             ', '2015-01-27 10:24:00', '92');
INSERT INTO `counts` VALUES ('475052', '107             ', '2015-01-27 10:26:00', '124');
INSERT INTO `counts` VALUES ('475058', '104             ', '2016-01-27 10:28:00', '98');
INSERT INTO `counts` VALUES ('475069', '107             ', '2016-01-27 10:32:00', '77');
INSERT INTO `counts` VALUES ('475075', '104             ', '2016-01-27 10:34:00', '44');
INSERT INTO `counts` VALUES ('475081', '107             ', '2016-01-27 10:36:00', '43');
INSERT INTO `counts` VALUES ('475087', '104             ', '2016-01-27 10:38:00', '89');
INSERT INTO `counts` VALUES ('475093', '107             ', '2016-01-27 10:40:00', '89');

Query 1:

SELECT
      YEAR(counts.ts) as SalesYear
    , SUM(case when counts.name = '104' then counts.count else 0 end) AS Res1 
    , SUM(case when counts.name = '107' then counts.count else 0 end) AS Res2
FROM counts 
WHERE counts.name IN ('104','107')
      AND counts.count > 0 
GROUP BY YEAR(counts.ts) 
ORDER BY YEAR(counts.ts)

Results:

| SalesYear | Res1 | Res2 |
|-----------|------|------|
|      2015 |  103 |  273 |
|      2016 |  231 |  209 |

Upvotes: 1

Related Questions