SQL group by date

Question

I am trying to make a report, but I have problems with my SQL architect.

I have a table that stores the closing date of the transaction.

I want to know how many transactions per month were like this:

SELECT trunct( closedate, 'MONTH' ) FROM  MY_TRANSACTIONS 

I am using oracle.

I get 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 COUNT () to select and GROUP BY at the end of the statement, which should“ do ”, but it’s not. I assume that each record is treated as a different value: -S

Any clues please?

Thanks.

Answer 1

You want to group all fields other than agg. And you do not want to truncate the date, you need the monthly part of the date.

so something like

to_char (datefield, 'Month'), count (*) ... group by_char (datefield, 'Month');