计算两个月数据的差异b / w

Question

I have a mysql table orders in that I have a column order_date which is current time stamp(2016-08-17 00:00:00.000000). now I want to select or count the data's entered this month and the previous month, after this I can find the difference between these two months I am using this code and it is not working.

    $sql="SELECT * FROM order WHERE order_date > DATE_SUB(NOW(), INTERVAL 1 MONTH)";
    $result = $this->db->query($sql);
    return $result;

this is not working an mysql error is produced.

Answer 1

First, the following is the correct logic to get all values from the current month and all of the previous month:

select *
from orders o
where order_date >= date_sub(date_sub(curdate(), interval day(curdate) - 1 day), interval 1 month);

Then, use conditional aggregation for comparison. Here is an easy way:

select sum(month(order_date) = month(curdate())) as cur_month,
       sum(month(order_date) <> month(curdate())) as prev_month,
       (sum(month(order_date) = month(curdate())) -
        sum(month(order_date) <> month(curdate()))
       ) as diff
from orders o
where order_date >= date_sub(date_sub(curdate(), interval day(curdate) - 1 day), interval 1 month);

Note: I don't fully see the utility of comparing a partial month (this month) to a full month (last month), but that is what you seem to be asking for. If you are asking for something different, then ask another question with sample data and desired results.