Help with Query

All postings related to Business One only

Moderators: Snowy, thx4allthefish

Post Reply
Niktosko
Posts: 5
Joined: Tue Oct 23, 2012 2:48 am

Help with Query

Post by 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

Post by 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]

Gothmog
Posts: 1955
Joined: Wed Sep 12, 2007 4:46 am
Location: Probably not home

Re: Help with Query

Post by 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

Niktosko
Posts: 5
Joined: Tue Oct 23, 2012 2:48 am

Re: Help with Query

Post by Niktosko » Tue Oct 23, 2012 7:48 am

Yea, this was really simple. Thanks. We can LOCK the post.

Post Reply