Help needed with Simple Query!

[JMac]

I'm a DBA and don't get much chance to write much in the way of SQL to get at the data so, here goes...

I have a date column, and order number column, a customer id...1 row for each order.

I want, basically, to group by date (Year - then Month within year)
And within this grouping show the individual customers and the number of orders each customer issued each month.

Sounds simple but I'm having trouble grouping.

Using 8.1.5.

[TomazZ]

select trunc(date,'mm'), customer, count(*) 
  from orders
 group by trunc(date,'mm'), customer

[JMac]

Why TRUNC and not TO_DATE(date,'MM')?
Or are they the same?

[TomazZ]

Have you tried the latter? I don't believe it works.

Maybe you meant to_char(date,'MM')? Still, this would sum all years' data together by months. You should use to_char(date,'YYYY.MM').

I prefer trunc, though.

[JMac]

I did mean TO_CHAR !!!

What I wanted was something like:

ORDER BY yy, mm

so that the orders would be displayed by year, and then month within year but GROUPED by customer number

e.g.

Dec 02 Customer 1 2 orders
Customer 6 9 orders

Jan 03 Customer 1 3 orders
Customer 3 1 orders

[TomazZ]

Well, I would use the first selected I posted.
If you need special formating of output, you can still use
select to_char(trunc(date,'mm'),'yyyy'),
to_char(trunc(date,'mm'),'MON')....
or something like that.
But I would still rather have trunc(date,'mm') in group by.
I believe for big orders table this sort would be faster than

select to_char(date,'yyyy'), to_char(date,'mm'), customer, count(*)
  from orders
 group by to_char(date,'yyyy'), to_char(date,'mm'), customer

while it will give you the same result.

Maybe I am wrong - after all, it's just a feeling.
I am sure abhaysk will be happy to test my assumptions.