-
Help needed with Simple Query!
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.
-
Code:
select trunc(date,'mm'), customer, count(*)
from orders
group by trunc(date,'mm'), customer
Tomaž
"A common mistake that people make when trying to design something completely
foolproof is to underestimate the ingenuity of complete fools" - Douglas Adams
-
Why TRUNC and not TO_DATE(date,'MM')?
Or are they the same?
-
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.
Tomaž
"A common mistake that people make when trying to design something completely
foolproof is to underestimate the ingenuity of complete fools" - Douglas Adams
-
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
-
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
Code:
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.
Tomaž
"A common mistake that people make when trying to design something completely
foolproof is to underestimate the ingenuity of complete fools" - Douglas Adams
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|