Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Group by Query Error Expand / Collapse
Author
Message
Posted Friday, February 13, 2009 11:16 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, August 10, 2009 8:51 PM
Points: 6, Visits: 31
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.




Post #656860
Posted Friday, February 13, 2009 11:25 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Wednesday, October 24, 2012 2:12 PM
Points: 1,213, Visits: 3,232
These error messages are pretty clear.
Read the error messages then look at your query.
Post #656875
Posted Friday, February 13, 2009 11:48 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Thursday, April 10, 2014 11:21 AM
Points: 61, Visits: 279
suresh,

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

In your case, remember that whenever you use the group by clause you need to add all the other colums as well except the function column (i.e. sum)
Post #656893
Posted Friday, February 13, 2009 1:00 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Thursday, February 13, 2014 5:53 PM
Points: 36, Visits: 144
What every one else said, and also ...

Why are you even trying to use a SUM ... when you're only looking at one row?
Post #656946
Posted Saturday, July 24, 2010 9:42 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Tuesday, March 18, 2014 7:01 AM
Points: 398, Visits: 281
Hi,
instead using all the columns in the group by clause, you can use aggregate function "OVER BY " in the query............
OR
give me the details of the output what u want.....
Post #958402
Posted Sunday, July 25, 2010 12:14 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Saturday, April 16, 2011 12:19 PM
Points: 16, Visits: 27
In group by clause, you should include all the columns present in the select list that are not aggregated.
So, in this example include all the columns in the group by clause except Sum(Value).
Post #958456
Posted Wednesday, August 04, 2010 2:36 AM


Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Tuesday, October 01, 2013 1:43 PM
Points: 18, Visits: 71
There is no sense the query you asked for, but it is possible to retrieve the info you need, just
build your query like the below one:

SELECT OrderFormNo
,ClientName
,OrderDate
,Product
,sum(Value)as Total
,InvoiceQuantity
,PendingQuantity
FROM AOM_Tbl_PendingOrders
GROUP BY OrderFormNo, ClientName,OrderDate, InvoiceQuantity, PendingQuantity, product

But remember there is no sense you get all the fields to sum. You must build a result set with some sense for example:


SELECT ClientName, OrderDate, Sum(value) as Total
FROM AOM_Tbl_PendingOrders
Group By ClientName, OrderDate

The above query is gathering All Orders made in a time stamp from customers and applying a sum on value field.
It has sense because you can count and know how much you have gain in that time for each Customer.

I know it is an old post, but I resolved to explain a little bit more why it had no sense like some folks were talking!

Regards


Best Regards,
Marcos Rosa / marcosfac@gmail.com
Post #963276
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse