João Aranda João Aranda - 1 month ago 11
MySQL Question

Multiple Select with same table in MySQL doesn't work

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:

Result that i need

Answer Source

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)

TEST: http://rextester.com/HCRIU11065