SQLize Online / PHPize Online  /  SQLtest Online

create table opers ( tr_viol_id bigint, oper_code integer, refuse_code integer, viol_datetime timestamp(0) ); select * from opers "c"; select t1.to_char(viol_datetime, 'yy-mm-dd'), count(oper_code) from opers t1 group by to_char(viol_datetime, 'yy-mm-dd'); /*left join ( select to_char(viol_datetime, 'yy-mm-dd'), count(tr_viol_id) from opers d where (viol_datetime between '2018-04-01 00:00:00' and '2018-04-20 23:59:59') and oper_code = 29 and refuse_code = 0 group by to_char(viol_datetime, 'yy-mm-dd') ) d on "c".to_char(viol_datetime, 'yy-mm-dd') = d.to_char(viol_datetime, 'yy-mm-dd'); */ select a.to_char(viol_datetime, 'yy-mm-dd'), a.count(tr_viol_id), b.count(refuse_code), c.count(oper_code), d.count(tr_viol_id) from opers a left join ( select b.to_char(viol_datetime, 'yy-mm-dd'), b.count(refuse_code), c.count(oper_code) from opers b left join ( select c.to_char(viol_datetime, 'yy-mm-dd'), c.count(oper_code), d.count(tr_viol_id) from opers c left join ( select to_char(viol_datetime, 'yy-mm-dd'), count(tr_viol_id) from opers d where (viol_datetime between '2018-04-01 00:00:00' and '2018-04-20 23:59:59') and oper_code = 29 and refuse_code = 0 group by to_char(viol_datetime, 'yy-mm-dd') ) d on c.to_char(viol_datetime, 'yy-mm-dd') = d.to_char(viol_datetime, 'yy-mm-dd') where (viol_datetime between '2018-04-01 00:00:00' and '2018-04-20 23:59:59') and oper_code = 29 group by to_char(viol_datetime, 'yy-mm-dd') ) c on b.to_char(viol_datetime, 'yy-mm-dd') = c.to_char(viol_datetime, 'yy-mm-dd') where (viol_datetime between '2018-04-01 00:00:00' and '2018-04-20 23:59:59') and refuse_code = 0 group by to_char(viol_datetime, 'yy-mm-dd') ) b on a.to_char(viol_datetime, 'yy-mm-dd') = b.to_char(viol_datetime, 'yy-mm-dd') where viol_datetime between '2018-04-01 00:00:00' and '2018-04-20 23:59:59' group by to_char(viol_datetime, 'yy-mm-dd')