sql统计计数

SELECT days,
count(CASE WHEN order_state = '2' THEN '已支付' end) AS '购票数' ,
count(CASE WHEN order_state = '5' THEN '已退票' end) AS '退票数',
count(CASE WHEN order_state= '8'  OR order_state= '9' THEN '已使用' end) AS '已使用'

 FROM
(
SELECT
    b.order_state,a.ex_time time,DATE_FORMAT(a.ex_time, '%Y-%m-%d') AS days
FROM
    ex_user_log a
LEFT JOIN ex_order b ON a.order_no = b.order_no
WHERE a.flag='1'
GROUP BY days,a.user_id 
-- ORDER BY days desc
UNION ALL
SELECT
    b.order_state order_state,a.refund_time time, DATE_FORMAT(refund_time, '%Y-%m-%d') AS days
FROM
    ex_order_refund a 
LEFT JOIN ex_order b
on a.old_order_no=b.order_no
GROUP BY
    days,
    b.user_id
-- ORDER BY days DESC
UNION ALL
SELECT
    order_state,pay_time time,DATE_FORMAT(pay_time, '%Y-%m-%d') AS days
FROM
    ex_order a
LEFT JOIN ex_viewer_reg b 
on a.open_id=b.open_id
WHERE 
    a.order_state = '2'
-- AND b.reg_mode='2'
GROUP BY days
) a 
GROUP BY days ORDER BY days DESC