aggregation problem

kurupt1 · #1 (**permalink**) 02-12-10, 07:44

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

pootle flump · #2 (**permalink**) 02-12-10, 07:47

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.

kurupt1 · #3 (**permalink**) 02-12-10, 08:48

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

pootle flump · #4 (**permalink**) 02-12-10, 08:53

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....

kurupt1 · #5 (**permalink**) 02-12-10, 09:07

Thank you, who knew it was that simple!

Internet Services

Web Development

Digital Marketing

Consumer Tech