Group By & SUM question

[martiros]

Hi everybody,

I need to select sales info from 2 tables and group it by year.
tables:
salesdetails
>>sale_id, qty_ordered, qty_shipped, date_shipped, sale_prod_id

products
>>prod_id, price

I need to show for EACH Year:
total qty of products ordered,
total qty of products shipped,
total value of products ordered (price* qty_ordered)

Here is my thing but it gives me a wrong results - much higher qty & total:

SELECT datepart(yy,date_shipped) as sales_year, sum(qty_ordered) as total_orders, sum(qty_shipped) as total_shipped, sum(price*qty_ordered) as total_sales
FROM salesdetails sd, products p
WHERE p.prod_id_id=sd.sale_prod_id
GROUP BY datepart(yy,date_shipped)

What I'm doing wrong?

 

[WildHare]

I don't see a join clause. How are your tables related?.....



Me? Ambivalent? Well, yes and no....
Another free Access forum:

http://www.forumco.com/jmhare

More Access stuff at

http://www.jmhare.com/wildhare.htm

 

[WildHare]

Just a follow up - I just tried this, and it worked fine when I One to Many'ed the Prods to Sales tables. I also used the YEAR(OrderDate) function instead of DatePart("yy", Orderdate):

SELECT Year([OrderDate]) AS OrderYear, Sum([Sales].[QtyOrdered]) AS SumOfQtyOrdered, Sum([Sales].[QtySHipped]) AS SumOfQtySHipped
FROM Products INNER JOIN Sales ON [Products].[ProdID]=[Sales].[ProID]
GROUP BY Year([OrderDate]);

I think your problem is without a join line, you get a cross join - every record of Sales to every Record of Parts... lotsa big numbers that way...



Me? Ambivalent? Well, yes and no....
Another free Access forum:

http://www.forumco.com/jmhare

More Access stuff at

http://www.jmhare.com/wildhare.htm

 

[martiros]

WildHare Thank you much!

Stupid me - I forgot about joining these tables...