Group by on Postgresql Date Time

Question

Hy. There are employee records in my postgresql database something like

 CODE     DATE       COUNT 
"3443"   "2009-04-02"  3
"3444"   "2009-04-06"  1
"3443"   "2009-04-06"  1
"3443"   "2009-04-07"  7

I want to use a query "SELECT ALL CODES AND COUNT THEM THAT OCCURRED IN THE MONTH"

RESULT:

 CODE     DATE     COUNT 
"3443"   "2009-04"  3
"3441"   "2009-04"  13
"3442"   "2009-04"  11
"3445"   "2009-04"  72

I did use a query i.e.

SELECT CODE,date_part('month',DATE),count(CODE)
FROM employee
where 
group by CODE,DATE

The above query runs fine but the months listed in the records are in form of numbers and its hard to find that a month belongs to which year. In short I want to get the result just like mention above in the RESULT section. Thanks

Answer 1

Try this:

SELECT CODE, to_char(DATE, 'YYYY-MM'), count(CODE)
FROM employee
where 
group by CODE, to_char(DATE, 'YYYY-MM')

Answer 2

Depending on whether you want the result as text or a date, you can also write it like this:

SELECT CODE, date_trunc('month', DATE), COUNT(*)
    FROM employee
    GROUP BY CODE, date_trunc('month', DATE);

Which in your example would return this, with DATE still a timestamp, which can be useful if you are going to do further calculations on it since no conversions are necessary:

 CODE     DATE     COUNT 
"3443"   "2009-04-01"  3
"3441"   "2009-04-01"  13
"3442"   "2009-04-01"  11
"3445"   "2009-04-01"  72

date_trunc() also accepts other values, for instance quarter, year etc. See the documentation for all values