May 12, 2003 at 5:23 pm
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?
the result should be like this:
year ordered shipped total
1999 459 443 $12,333.12
2000 654 557 $23,412.92
Thank you
May 13, 2003 at 3:04 am
Try this (quick solution untested)
SELECT a.sales_year,
sum(a.qty_ordered) as total_orders,
sum(a.qty_shipped) as total_shipped,
sum(a.price) as total_sales
FROM (
SELECT datepart(yy,sd.date_shipped) as sales_year,
sd.qty_ordered,
sd.qty_shipped,
(p.price*sd.qty_ordered) as price
FROM salesdetails sd INNER JOIN products p ON p.prod_id=sd.sale_prod_id
) a
GROUP BY a.sales_year
Far away is close at hand in the images of elsewhere.
Anon.
May 13, 2003 at 3:47 am
I'm probably missing something, but your initial query seems to be correct to me.
Are you sure that this is not due to the data in your tables. Maybe there is a duplicate record in products?
May 13, 2003 at 4:17 am
Good one NPeeters, missed that one in my haste. I have had problems before with joins and duplicates/aggregation and thought this was similar. Thinking about it my solution would give the same problem if your right about the data. My apologies.
Far away is close at hand in the images of elsewhere.
Anon.
May 13, 2003 at 4:32 am
I agree your query looks right.
But try changing
FROM salesdetails sd, products p
WHERE p.prod_id_id=sd.sale_prod_id
to
FROM salesdetails sd
INNER JOIN products p
ON p.prod_id_id=sd.sale_prod_id
to see what happens.
Also what do you end up with?
May 13, 2003 at 1:25 pm
Thank you very much for helping me.
Here is what I eneded up with and it works as it should:
SELECT Year(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 products p, salesdetails sd
WHERE p.product_id=* sd.product_id
GROUP BY Year(date_shipped)
The trick was in a type of join >> "=*"
May 13, 2003 at 1:38 pm
Oh, by the way...
I'm not sure if this operator (=*) will work within any DB or I have to use Right or Left Join terms?
I was testing it on Sybase but are going to use it on SQL server & Access
May 13, 2003 at 3:29 pm
You should then covert what I said to LEFT JOIN I do believe and should be the same.
May 15, 2003 at 9:17 am
The "outer join" solution shouldn't have been necessary.
All sales-detail product id's should have been in the products master table making the outer join unnecessary.
Seems to me there are referential integrity problems and orphaned sales-detail rows somewhere.
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply