I think it should be very easy, but it didn't work at all. I have one table and I'd like to generate a chart which are displaying grouped items and with one where clause (column1) it should display the entries with status 0, and with the other where clause (column2) it should display the entries with status 1.
If I do the just one query it works fine, but I'd like to have both (status 0 and status 1) in one combined query.
Status 0 query:
SELECT item,quantity, COUNT(status) FROM `table`
WHERE `retry` = 1
AND `status` = 0
GROUP BY item,quantity
ORDER BY COUNT(status) DESC
Status 1 query:
SELECT item,quantity, COUNT(status) FROM `table`
WHERE `retry` = 1
AND `status` = 1
GROUP BY item,quantity
ORDER BY COUNT(status) DESC
My try to combine both (didn'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
check this please , would it work for you?
please try this