Group by Query Error

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2005 » T-SQL (SS2K5) » Group by Query Error

Group by Query Error

Rate Topic

Display Mode

Topic Options

Author

Message

suresh.s-895438

Posted Friday, February 13, 2009 11:16 AM

Forum 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

andrewd.smith

Posted Friday, February 13, 2009 11:25 AM

Ten 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

sam-770000

Posted Friday, February 13, 2009 11:48 AM

Valued Member

Group: General Forum Members
Last Login: Monday, May 06, 2013 10:31 AM
Points: 61, Visits: 217

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

bvaljalo-1000038

Posted Friday, February 13, 2009 1:00 PM

SSC Rookie

Group: General Forum Members
Last Login: Wednesday, July 20, 2011 3:23 PM
Points: 36, Visits: 138

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

VENUGOPAL.RR

Posted Saturday, July 24, 2010 9:42 AM

Old Hand

Group: General Forum Members
Last Login: Saturday, April 13, 2013 10:02 AM
Points: 398, Visits: 277

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

amrata88

Posted Sunday, July 25, 2010 12:14 AM

Grasshopper

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

Marcos Leandro Rosa

Posted Wednesday, August 04, 2010 2:36 AM

Grasshopper

Group: General Forum Members
Last Login: Thursday, July 12, 2012 12:03 PM
Points: 17, Visits: 70

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 »

Permissions