MySQL selects and counts 2 columns with different clauses
I think it should be very simple, but it didn't work. I have one table and I would like to create a chart that displays grouped items and with one where clause (column1) it should display records with status 0 and with another where clause (column2) it should display records with status 1 ...
If I only make one request, it works fine, but I would like to have both (status 0 and status 1) in one combined request.
Status request 0:
SELECT item,quantity, COUNT(status) FROM `table`
WHERE `retry` = 1
AND `status` = 0
GROUP BY item,quantity
ORDER BY COUNT(status) DESC
Status Request 1:
SELECT item,quantity, COUNT(status) FROM `table`
WHERE `retry` = 1
AND `status` = 1
GROUP BY item,quantity
ORDER BY COUNT(status) DESC
My attempt to combine both (doesn't work)
SELECT t1.item,t1.quantity, COUNT(t2.status), COUNT(t3.status) FROM `table` AS t1
LEFT JOIN (SELECT item,status FROM `table` WHERE `status` = '0' AND `retry` = 1 GROUP BY item,quantity) AS t2
ON t1.ndc = t2.ndc
LEFT JOIN (SELECT item,status FROM `table` WHERE `status` = '1' AND `retry` = 1 GROUP BY item,quantity) AS t3
ON t1.ndc = t3.ndc
WHERE 1
GROUP BY t1.item,t1.quantity
ORDER BY COUNT(t2.status) DESC
+3
source to share
2 answers
SQL Fiddle Testdata:
CREATE TABLE testtable
(`id` int, `item` bigint(15), `quantity` double,`status` int(2),`retry` int(2))
;
INSERT INTO testtable
(`id`, `item`, `quantity`, `status`, `retry`)
VALUES
(1, '452457824', '1.0', '1', '1'),
(2, '452457824', '1.0', '1', '1'),
(3, '452457824', '0.5', '1', '1'),
(4, '452457824', '0.5', '0', '1'),
(5, '452457824', '0.5', '0', '1'),
(6, '452457824', '0.5', '0', '1'),
(7, '21432423', '1.0', '1', '1'),
(8, '21432423', '1.0', '1', '1'),
(9, '21432423', '1.0', '0', '1'),
(10, '21432423', '1.0', '0', '1'),
(11, '3455467567', '2.0', '1', '1'),
(12, '3455467567', '2.0', '1', '1'),
(13, '3455467567', '2.0', '0', '1'),
(14, '3455467567', '2.0', '0', '1'),
(15, '3455467567', '2.0', '0', '1'),
(16, '3455467567', '1.0', '1', '1'),
(17, '3455467567', '1.0', '1', '1'),
(18, '3455467567', '1.0', '1', '1')
;
0
source to share