Group by error - 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

SQL Server 2005 Forums

Transact-SQL (2005)

Group by error

Author

Topic

suresh_dotnet
Starting Member

6 Posts

Posted - 2009-02-13 : 13:15:57

here please refer the table:

in that table value:

1 is Order No

ABCL is Client Name

12/1/2009 12:00:00 AM is OrderDate

ARMIS - is Product

1000 is Value

1 is InvoiceQuantity
1 is Pendingquantity

1 ABCL 12/1/2009 12:00:00 AM ARMIS 10000 1 1
1 HAL 12/1/2009 12:00:00 AM ARMIS 2000 2 2
1 NAL 12/1/2009 12:00:00 AM TimeCheck 20000 2 2
2 HidusthanLeaver 12/1/2009 12:00:00 AM ARMIS 2000 2 2
2 IOC 12/1/2009 12:00:00 AM TimeCheck 30000 2 2
3 BEML 12/1/2009 12:00:00 AM ARMIS 400000 2 2
3 ABCL 12/1/2009 12:00:00 AM TimeCheck 500000 3 3

I am using the following Groupby Query

select OrderFormNo,ClientName,OrderDate,Product,sum(Value),
InvoiceQuantity,PendingQuantity from AOM_Tbl_PendingOrders group by OrderFormNo, ClientName,OrderDate

I have got the erreor below

Msg 8120, Level 16, State 1, Line 1
Column 'AOM_Tbl_PendingOrders.Product' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.
Msg 8120, Level 16, State 1, Line 1
Column 'AOM_Tbl_PendingOrders.InvoiceQuantity' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.
Msg 8120, Level 16, State 1, Line 1
Column 'AOM_Tbl_PendingOrders.PendingQuantity' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

Can any one solve this issue.

suresh

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-02-13 : 13:24:38

you cant use columns directly in select list unless they're included in group by. so modify like-


select OrderFormNo,ClientName,OrderDate,Product,sum(Value),
InvoiceQuantity,PendingQuantity from AOM_Tbl_PendingOrders 
group by OrderFormNo, ClientName,OrderDate,InvoiceQuantity,PendingQuantity

bklr
Master Smack Fu Yak Hacker

1693 Posts

Posted - 2009-02-13 : 23:35:30

slight modification to visakh code

select OrderFormNo,ClientName,OrderDate,Product,sum(Value),InvoiceQuantity,
PendingQuantity from AOM_Tbl_PendingOrders
group by OrderFormNo, ClientName,OrderDate,Product,InvoiceQuantity,PendingQuantity

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-02-14 : 01:01:19

I'm still not sure this wil give you results you want. what according to you must be value returned for sum(Value)? As of now, the query given gives sum of value field for each combination of OrderFormNo, ClientName,OrderDate,Product,InvoiceQuantity,PendingQuantity field values

Nageswar9
Aged Yak Warrior

600 Posts

Posted - 2009-02-14 : 04:38:10

Hi suresh_dotnet,
Can u Explain what's ur requirement....

shaihan
Starting Member

20 Posts

Posted - 2009-02-14 : 09:13:04

your question is not clear. i agree with visakh16 as it gives you a combined sum of value. what are the criteria for calculating the sum? for e.g. if you want to calculate against products ordered by clients then use only clientname and product in group by clause.

SELECT ClientName, Product, sum(value)
FROM AOM_Tbl_PendingOrders
GROUP BY ClientName, Product

this will give you sum of value for each products ordered by each clients. you can use this query as a inner query and then use join to get other column values.

S ]-[ /-\ | ]-[ /-\ N

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources