SQL group by "date" question

Hello.

I'm trying to make a report but I'm having problems with my archi nemesis SQL.

I have a table where the close date of a transaction is stored.

I want to know how many transaction per month there was so I did:

SELECT trunct( closedate, 'MONTH' ) FROM  MY_TRANSACTIONS

I'm using oracle.

I'm getting a list like this:

2002-09-01 00:00:00.0
2002-09-01 00:00:00.0
...
2002-10-01 00:00:00.0
2002-10-01 00:00:00.0
...
2002-11-01 00:00:00.0
2002-11-01 00:00:00.0

etc.

So I thought "If I add a COUNT( ) in the select and GROUP BY at the end of the statement that should do" but it doesn't. My guess is because each record is treated as a different value : -S

Any hint please?

Thanks.

From stackoverflow

• You want to group by all non-agg fields. And you don't want to truncate the date, you want the month part of the date.

  so something like

  select to_char(datefield, 'Month'), count(*) from ... group by to_char(datefield, 'Month');

  OscarRyz : @Arnshea: Thanks. Do you know what's the oracle equivalent for "datepart"???

  Arnshea : to_char(datefield, 'Month') should do it

  OscarRyz : @Arnshea: It did ( partially ) because I think is sum all decembers from the last years...

  Eric Petroelje : Try to_char(datefield, 'MONTH YYYY')

  OscarRyz : Thanks a lot... I was doing ( by intuition ) doing exactly that Eric. :)

  WW : It's better form to do: TRUNC(datefield, 'Mon') to truncate the days, hours, minutes, seconds from the date.

  Jeffrey Kemp : TRUNC will be slightly more efficient, but then you'll probably want to format the output anyway...