SQL group by "date" question

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.

Answer 1

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');