Help needed with Simple Query!
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 6 of 6

Thread: Help needed with Simple Query!

  1. #1
    Join Date
    Jan 2000
    Location
    Chester, England.
    Posts
    818

    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.


  2. #2
    Join Date
    Jul 2001
    Location
    Slovenia
    Posts
    422
    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

  3. #3
    Join Date
    Jan 2000
    Location
    Chester, England.
    Posts
    818
    Why TRUNC and not TO_DATE(date,'MM')?
    Or are they the same?

  4. #4
    Join Date
    Jul 2001
    Location
    Slovenia
    Posts
    422
    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

  5. #5
    Join Date
    Jan 2000
    Location
    Chester, England.
    Posts
    818
    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

  6. #6
    Join Date
    Jul 2001
    Location
    Slovenia
    Posts
    422
    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
  •  



Click Here to Expand Forum to Full Width