agungandika agungandika - 5 months ago 9
SQL Question

Getting Row Count by SubQuery which has GROUP BY - MySQL

I currently have a table with following schema:

CREATE TABLE `order_handling` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`oh_no` varchar(24) COLLATE utf8_unicode_ci DEFAULT NULL,
`date_request` datetime DEFAULT NULL,
`status` smallint(4) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=14 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;


I want to perform query that will result like this:

+-------------------------------------------------------------+
| MonthDone | OhProgress | OhHold | OhCancel | OhDone | Total |
+-------------------------------------------------------------+
| June 2016 | 2 | 1 | 1 | 3 | 7 |
| July 2016 | 6 | 1 | null | null | 7 |
+-------------------------------------------------------------+


My Query is,

SELECT
DATE_FORMAT(`oh`.`date_request`, '%m-%Y') AS `IdMonth`,
(SELECT COUNT(*) FROM order_handling as oh1 WHERE `oh1`.`status` IN (1,2) GROUP BY IdMonth) As OhProgress,
(SELECT COUNT(*) FROM order_handling as oh2 WHERE `oh2`.`status` IN (3) GROUP BY IdMonth) As OhHold,
(SELECT COUNT(*) FROM order_handling as oh3 WHERE `oh3`.`status` IN (4) GROUP BY IdMonth) As OhCancel,
(SELECT COUNT(*) FROM order_handling as oh3 WHERE `oh3`.`status` IN (5) GROUP BY IdMonth) As OhDone,
(SELECT COUNT(*) FROM order_handling as oh5 WHERE `oh5`.`status` IN (1,2,3,4,5) GROUP BY IdMonth) As SumTotal
FROM `order_handling` `oh`
GROUP BY DATE_FORMAT(`oh`.`date_request`, '%M %Y')
ORDER BY `oh`.`date_request` ASC


The output was not expected. If I change SubQuery GROUP BY with
DATE_FORMAT(date_request, '%m-%Y')
like follows:

SELECT
DATE_FORMAT(`oh`.`date_request`, '%m-%Y') AS `IdMonth`,
(SELECT COUNT(*) FROM order_handling as oh1 WHERE `oh1`.`status` IN (1,2) GROUP BY DATE_FORMAT(`oh1`.`date_request`, '%m-%Y')) As OhProgress,
(SELECT COUNT(*) FROM order_handling as oh2 WHERE `oh2`.`status` IN (3) GROUP BY DATE_FORMAT(`oh2`.`date_request`, '%m-%Y')) As OhHold,
(SELECT COUNT(*) FROM order_handling as oh3 WHERE `oh3`.`status` IN (4) GROUP BY DATE_FORMAT(`oh3`.`date_request`, '%m-%Y')) As OhCancel,
(SELECT COUNT(*) FROM order_handling as oh4 WHERE `oh4`.`status` IN (5) GROUP BY DATE_FORMAT(`oh4`.`date_request`, '%m-%Y')) As OhDone,
(SELECT COUNT(*) FROM order_handling as oh5 WHERE `oh5`.`status` IN (1,2,3,4,5) GROUP BY DATE_FORMAT(`oh5`.`date_request`, '%m-%Y')) As SumTotal
FROM `order_handling` `oh`
GROUP BY DATE_FORMAT(`oh`.`date_request`, '%M %Y')
ORDER BY `oh`.`date_request` ASC


It gives me Error: Subquery return more than 1 row
Here is my SQLFiddle

Please help,
thank you

Answer

No need for subqueries

SELECT
    DATE_FORMAT(`date_request`, '%m-%Y') AS `IdMonth`,
   SUM(status IN (1,2) ) As OhProgress,
    SUM(status IN (3) ) As OhHold,
    SUM(status IN (4) ) As OhCancel,
    SUM(status IN (5) ) As OhDone,
    SUM(status IN (1,2,3,4,5)) As SumTotal
FROM `order_handling` 
GROUP BY DATE_FORMAT(`date_request`, '%M %Y')
ORDER BY `date_request` ASC

SQLFiddle (courtesy of @Giorgos)

Comments