Cheat Sheet

Sunday, April 2, 2017

Postgresql generate series

SELECT to_char(d.serie, 'Mon-YYYY'), count(r.report_kes_id) FROM 
(SELECT serie, extract(year FROM serie) as tahun , extract(Month FROM serie) as bulan FROM generate_series(date '2013-01-01', date '2015-12-31' , interval '1 month') serie) d
LEFT JOIN  rekod.dadah_klien r ON r.tarikhperintah = d.tahun AND r.bulan_ks = d.bulan 
   GROUP BY d.serie ORDER BY d.serie

No comments:

Post a Comment