Results 1 to 5 of 5
  1. #1
    Join Date
    Feb 2010
    Posts
    3

    Unanswered: aggregation problem

    Hi guys,

    i've been playing around to find a solution and going through forums but no joy. hopefully you can help

    I have a db of customer deliveries by month. the delivery table will look something like:
    dmonth customerid qty
    mar-10 1 1
    jun-10 2 1
    mar-10 1 2
    sep-10 1 1

    Obviously i then have another table with all the customer info, in particular name and id.

    Additionally I have a table that gives me months and quarters:
    dmonth quarter
    mar-10 2010q1
    apr-10 2010q2

    etc

    What i want to get at is customer orders by customer and quarter. I can get total orders by quarter by joining the monthquarter table and the delivery table, but i cant figure out how to get it by customer as well.

    Hopefully i've explained it clearly, appreciate any help.

    thanks

  2. #2
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    If you post up what you have that would be a good start. It sounds like you are almost there but seeing the query will help.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  3. #3
    Join Date
    Feb 2010
    Posts
    3
    So far I have this, which gives me total quantities for each quarter. To get it by customer as well


    Code:
    SELECT MonthQuarter.Quarter, sum(Delivery.Qty) As QtyQuarterly 
    FROM Delivery INNER JOIN MonthQuarter ON Delivery.DMonth = MonthQuarter.DMonth 
    GROUP BY MonthQuarter.Quarter
    My guess to get it by CustomerID as well was to add Delivery.CustomerID to the SELECT, but that didn't work

  4. #4
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Half way there - you were not wrong to add it to the SELECT clause.

    Every column in the SELECT clause must also be an aggregate (such as SUM()) or it must be in the GROUP BY.
    So....
    Testimonial:
    pootle flump
    ur codings are working excelent.

  5. #5
    Join Date
    Feb 2010
    Posts
    3
    Thank you, who knew it was that simple!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •