How to use Group by clause - 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

How to use Group by clause

Author

Topic

sha_agrawal
Starting Member

24 Posts

Posted - 2009-04-13 : 09:31:34

I am using SQL Server 2000
I am having 2 table i.e. Sale Table and Item Table

Sale Table containing ItemID and Sold Qty,Sale Date
Item Table containing ItemID,Item Name,Unit(Kg.,MT.,Lt. etc.)

My expected output is

Item Name ----Total Sold Qty---------Unit
XYZ --------------- 20 ---------M.T.
ABC ----------------10 ---------KG.

I tried this query
SELECT Item.ItemID,ItemName,SUM(SoldQty),Unit FROM Item,Sale
WHERE Item.ItemID=Sale.ItemID GROUP BY Item.ItemID

It throws error 'Unit,itemname not included in aggregate function'

Pls.help me how to get desired output.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-04-13 : 09:34:49

SELECT ItemName,SUM(SoldQty),Unit FROM Item,Sale
WHERE Item.ItemID=Sale.ItemID GROUP BY ItemName

Madhivanan

Failing to plan is Planning to fail

sha_agrawal
Starting Member

24 Posts

Posted - 2009-04-13 : 09:50:45

Thanks madhivanan
It still gives error "Unit not included in aggreagate function or group by clause"

(1)What about Unit column
(2)In my table some items having same name but their manufacturer
are diffent so it is necessary to group by ItemID.

Please help me.

karthik_padbanaban
Constraint Violating Yak Guru

263 Posts

Posted - 2009-04-13 : 09:59:10

SELECT Itemid,ItemName,SUM(SoldQty),Unit FROM Item,Sale
WHERE Item.ItemID=Sale.ItemID GROUP BY Itemid,ItemName,Unit

add this too and check

Karthik

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources