i'm having a problem with my database's multiple selection. I need to do a select that returns a kind of table with some processed data, and it need to be ordered by day of month. To do this, i'm using multiple select's issue of mysql. This is my code:
SELECT
(SELECT COUNT( * ) FROM `table` WHERE `type`=1 AS 'Total',
(SELECT COUNT( * ) FROM `table` WHERE `type`=1 and `status` = 0 and `status_cancel` = 0 AS 'Open',
(SELECT COUNT( * ) FROM `table` WHERE `type`=1 and `status_cancel` = 1 AS 'Cancel',
(SELECT COUNT( * ) FROM `table` WHERE `type`=1 and `date_finish` is not null and `status_cancel` = 0 AS 'Finish',
(SELECT COUNT( * ) FROM `table` WHERE `type`=1 and `result` >= 0 and `date_finish` is not null and `status_cancel` = 0 AS 'Win',
(SELECT COUNT( * ) FROM `table` WHERE `type`=1 and `result` < 0 and `date_finish` is not null and `status_cancel` = 0 AS 'Loss'
Now it's returning the total of all rows in my table, but i can't do this return it grouped by day, help me, please!
The result must be like this:
I think you are saying how can I group by date when my queries are all amounting to sub queries. I would change it to be case statements for that. Try something like this
SELECT DATE(date_finish) as `date`, count(*) as 'Total',
SUM(CASE WHEN `status` = 0 AND `status_cancel` = 0 THEN 1 ELSE 0 END) as 'Open',
SUM(CASE WHEN `status_cancel` = 1 THEN 1 ELSE 0 END) as 'Cancel',
SUM(CASE WHEN `date_finish` is not null and `status_cancel` = 0 THEN 1 ELSE 0 END) as 'Finish',
SUM(CASE WHEN `result` >= 0 AND `date_finish` is not null AND `status_cancel` = 0 THEN 1 ELSE 0 END) as 'Win',
SUM(CASE WHEN `result` < 0 AND `date_finish` is not null AND `status_cancel` = 0 THEN 1 ELSE 0 END) as 'Loss'
from `table`
WHERE `type` = 1
group by DATE(date_finish)