I have 1 table that have a lot of records, I want to separate the COUNT(*)
value (profit/loss), I can do it with 2 MySQL query, but I want to do it with only 1 query, is that possible?
$query_string = '
SELECT
MONTH(order_time) AS month,
CASE WHEN order_profit > 0 THEN COUNT(*) END AS profit,
CASE WHEN order_profit < 0 THEN COUNT(*) END AS loss,
COUNT(*) AS total
FROM
trade_histories
';
$query_string .= '
WHERE
order_profit IS NOT NULL
';
$query_string .= '
GROUP BY
MONTH(order_time)
';
This is what I've tried so far, and this is the result
stdClass Object ( [month] => 1 [profit] => 447 [loss] => [total] => 447 )
stdClass Object ( [month] => 2 [profit] => 1153 [loss] => [total] => 1153 )
stdClass Object ( [month] => 3 [profit] => 898 [loss] => [total] => 898 )
stdClass Object ( [month] => 4 [profit] => [loss] => 640 [total] => 640 )
stdClass Object ( [month] => 12 [profit] => [loss] => 2 [total] => 2 )