Saturday, September 3, 2016

MYSQL- query for last one month count,last 60days,Feb month report generation








select SUM(CASE WHEN (DATE_FORMAT(order_date,'%m-%d-%y') > DATE_FORMAT(CURRENT_DATE - INTERVAL 30 DAY,'%m-%d-%y')) THEN 1 ELSE 0 END) AS ord,age from order1 group by age ;



select SUM(CASE WHEN (DATE_FORMAT(order_date,'%m-%d-%y') > DATE_FORMAT(CURRENT_DATE - INTERVAL 60 DAY,'%m-%d-%y')) THEN 1 ELSE 0 END) AS ord_from_60_days,SUM(CASE WHEN (DATE_FORMAT(order_date,'%m-%d-%y') > DATE_FORMAT(CURRENT_DATE - INTERVAL 30 DAY,'%m-%d-%y')) THEN 1 ELSE 0 END) AS ord_from_1month,age from order1 group by age ;


+------------------+-----------------+------+
| ord_from_60_days | ord_from_1month | age |
+------------------+-----------------+------+
| 7 | 4 | 23 |
| 0 | 0 | 25 |
+------------------+-----------------+------+
2 rows in set (0.00 sec)