GROUP problem - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

GROUP problem

Author

Topic

elic05
Yak Posting Veteran

62 Posts

Posted - 2008-11-03 : 12:59:00

I have 3 tables

1. Orders OrderID (PK),clientID,BuyingDate ,totalPrice
2. OrdersItems OrderID (FK),ProductID (FK),NoOfUnits,price
3. myProducts productID (PK),mainCategory,subCategory,name_en

I want to get a list of ProductIDs and number of units that where sold since specific date and where the product belongs to specific mainCategory.

This query does the job:

SELECT     oi.ProductID, SUM(oi.NoOfUnits) AS Expr1
FROM         Orders AS o INNER JOIN
                      OrderItems AS oi ON o.OrderID = oi.OrderID INNER JOIN
                      myProducts AS p ON p.productID = oi.ProductID 
WHERE     (o.BuyingDate >= '1/1/2008') AND (p.mainCategory = @mainCategory)
GROUP BY oi.ProductID

the problem arise when i try to list also the product names
i simply tried to add in the first line
SELECT oi.ProductID,p.name_en, SUM(oi.NoOfUnits) AS Expr1

I get an error
the column p.name_en is invalid because it is not contained in either an aggregation function or the group by clause

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-11-03 : 13:05:04

[code]SELECT t.ProductID,p.name_en,t.Expr1
FROM (SELECT oi.ProductID, SUM(oi.NoOfUnits) AS Expr1
FROM OrderItems oi
INNER JOIN Orders AS o
ON o.OrderID = oi.OrderID
WHERE (o.BuyingDate >= '1/1/2008')
GROUP BY oi.ProductID )AS t
INNER JOIN myProducts AS p ON p.productID = oi.ProductID
WHERE (p.mainCategory = @mainCategory)
[/code]

elic05
Yak Posting Veteran

62 Posts

Posted - 2008-11-03 : 13:17:51

I get an error

the multi-part undentifier oi.ProductID could not be bound

elic05
Yak Posting Veteran

62 Posts

Posted - 2008-11-03 : 13:40:47

one little error
in the line
INNER JOIN myProducts AS p ON p.productID = oi.ProductID

it has to be t.ProductID

and your solution is working fine

thanks again
you are very kind

if i want to get the 10 best seller products
i added the line
ORDER BY t.Expr1 DESC
can i get the only 10 lists

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-11-03 : 23:21:10

[code]SELECT TOP 10 t.ProductID,p.name_en,t.Expr1
FROM (SELECT oi.ProductID, SUM(oi.NoOfUnits) AS Expr1
FROM OrderItems oi
INNER JOIN Orders AS o
ON o.OrderID = oi.OrderID
WHERE (o.BuyingDate >= '1/1/2008')
GROUP BY oi.ProductID )AS t
INNER JOIN myProducts AS p ON p.productID = oi.ProductID
WHERE (p.mainCategory = @mainCategory)
ORDER BY t.Expr1 DESC[/code]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources