This website is not affiliated with, sponsored by, or approved by SAP AG.

Help with Query

All postings related to Business One only

Moderators: Snowy, thx4allthefish

Help with Query

Postby Niktosko » Tue Oct 23, 2012 3:09 am

Hi, I am trying to make a query which will list all my items grouped by the item group and want to see the sum of value on stock (for each item group). I have been trying in the query generator for a while but I am still not able to get those sum in it.
What is please wrong on this?
SELECT T0.[ItemCode], T0.[ItemName], T0.[AvgPrice], T1.[OnHand], T1.[OnHand]* T0.[AvgPrice] as StockValue, T0.[ItmsGrpCod] FROM OITM T0 INNER JOIN OITW T1 ON T0.ItemCode = T1.ItemCode GROUP BY T0.[ItmsGrpCod] ORDER BY T0.[ItemCode], T0.[ItemName], T0.[AvgPrice], T1.[OnHand]

I get the error message in German:
1). [Microsoft][SQL Server Native Client 10.0][SQL Server]Die OITM.ItemCode-Spalte ist in der Auswahlliste ungültig, da sie nicht in einer Aggregatfunktion und nicht in der GROUP BY-Klausel enthalten ist. 2). [Microsoft][SQL Server Native Client 10.0][SQ

Thanks in advance.
Niktosko
 
Posts: 5
Joined: Tue Oct 23, 2012 2:48 am

Re: Help with Query

Postby Niktosko » Tue Oct 23, 2012 5:06 am

Ok. I still miss some explanation, why this was not working for me. But found now my solution to get the result:

SELECT T0.[ItmsGrpCod], SUM(T0.[OnHand]*T0.[AvgPrice]) AS StockValue FROM OITM T0 GROUP BY T0.[ItmsGrpCod] ORDER BY T0.[ItmsGrpCod]
Niktosko
 
Posts: 5
Joined: Tue Oct 23, 2012 2:48 am

Re: Help with Query

Postby Gothmog » Tue Oct 23, 2012 6:51 am

You were trying to select the item code, but grouping lines by item group - you don't have one item code to select if you're grouping several items.
68 74 74 70 3a 2f 2f 74 69 6e 79 75 72 6c 2e 63 6f 6d 2f 62 64 6f 37 6d 77 67
Gothmog
 
Posts: 1942
Joined: Wed Sep 12, 2007 4:46 am
Location: Probably not home

Re: Help with Query

Postby Niktosko » Tue Oct 23, 2012 7:48 am

Yea, this was really simple. Thanks. We can LOCK the post.
Niktosko
 
Posts: 5
Joined: Tue Oct 23, 2012 2:48 am


Return to Business One

Who is online

Users browsing this forum: No registered users and 1 guest





loading...


This website is not affiliated with, sponsored by, or approved by SAP AG.