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 to_char(a.viol_datetime, 'yy-mm-dd'), count(a.tr_viol_id), count(b.refuse_code), count(c.oper_code), count(d.tr_viol_id) from opers a left join ( select to_char(b.viol_datetime, 'yy-mm-dd'), count(b.refuse_code), count(c.oper_code) from opers b left join ( select to_char(c.viol_datetime, 'yy-mm-dd'), count(c.oper_code), count(d.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 to_char(c.viol_datetime, 'yy-mm-dd') = to_char(d.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 to_char(b.viol_datetime, 'yy-mm-dd') = to_char(c.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 to_char(a.viol_datetime, 'yy-mm-dd') = to_char(b.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')