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

Szymon Lipiński · Accepted Answer · 2013-06-19 06:30:25Z

18

Try this:

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

answered Jun 19, 2013 at 6:30

Szymon Lipiński

28.8k17 gold badges81 silver badges82 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Jimmy Stenke · Accepted Answer · 2013-06-19 09:23:18Z

13

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

answered Jun 19, 2013 at 9:23

Jimmy Stenke

11.3k2 gold badges27 silver badges20 bronze badges

1 Comment

Melebius Over a year ago

It is possible to specify date_trunc('month', DATE) AS mydate and then use mydate in the rest of the query (e.g. in GROUP BY).

Martin Strejc · Accepted Answer · 2013-06-19 06:32:14Z

1

Try any of

SELECT CODE,count(CODE),
    DATE as date_normal, 
    date_part('year', DATE) as year, 
    date_part('month', DATE) as month, 
    to_timestamp(
        date_part('year', DATE)::text 
        || date_part('month', DATE)::text, 'YYYYMM')
        as date_month
FROM employee
where 
group by CODE,DATE;

answered Jun 19, 2013 at 6:32

Martin Strejc

4,3872 gold badges26 silver badges39 bronze badges

Collectives™ on Stack Overflow

Group by on Postgresql Date Time

3 Answers 3

Comments

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related