SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Group by Query Error


Group by Query Error

Author
Message
suresh.s-895438
suresh.s-895438
Grasshopper
Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)

Group: General Forum Members
Points: 14 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.
andrewd.smith
andrewd.smith
SSCommitted
SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)

Group: General Forum Members
Points: 1558 Visits: 3232
These error messages are pretty clear.
Read the error messages then look at your query.
sam-770000
sam-770000
SSC-Enthusiastic
SSC-Enthusiastic (108 reputation)SSC-Enthusiastic (108 reputation)SSC-Enthusiastic (108 reputation)SSC-Enthusiastic (108 reputation)SSC-Enthusiastic (108 reputation)SSC-Enthusiastic (108 reputation)SSC-Enthusiastic (108 reputation)SSC-Enthusiastic (108 reputation)

Group: General Forum Members
Points: 108 Visits: 386
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)
bvaljalo-1000038
bvaljalo-1000038
SSC Rookie
SSC Rookie (46 reputation)SSC Rookie (46 reputation)SSC Rookie (46 reputation)SSC Rookie (46 reputation)SSC Rookie (46 reputation)SSC Rookie (46 reputation)SSC Rookie (46 reputation)SSC Rookie (46 reputation)

Group: General Forum Members
Points: 46 Visits: 152
What every one else said, and also ...

Why are you even trying to use a SUM ... when you're only looking at one row?
VENUGOPAL.RR
VENUGOPAL.RR
SSC-Addicted
SSC-Addicted (432 reputation)SSC-Addicted (432 reputation)SSC-Addicted (432 reputation)SSC-Addicted (432 reputation)SSC-Addicted (432 reputation)SSC-Addicted (432 reputation)SSC-Addicted (432 reputation)SSC-Addicted (432 reputation)

Group: General Forum Members
Points: 432 Visits: 307
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.....
amrata88
amrata88
Grasshopper
Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)

Group: General Forum Members
Points: 20 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).
Marcos Leandro Rosa
Marcos Leandro Rosa
Valued Member
Valued Member (62 reputation)Valued Member (62 reputation)Valued Member (62 reputation)Valued Member (62 reputation)Valued Member (62 reputation)Valued Member (62 reputation)Valued Member (62 reputation)Valued Member (62 reputation)

Group: General Forum Members
Points: 62 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

Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search