Query Problem- trying to group by Item Group

[Former Member]

Hi All

I am creating a report which details the Item code, Item Description, In stock amount, Commited amount and the Price one particular price list. I want this to be grouped by the Item Group. The query below is the one I have created. It works well until i add anything into the Group by section.

SELECT T1.[ItemCode],

T1.[ItemName],

T2.[OnHand],

T2.[IsCommited],

T0.[Price]

FROM ITM1 T0 INNER JOIN

OITM T1 ON T0.ItemCode = T1.ItemCode

INNER JOIN OITW T2 ON T1.ItemCode = T2.ItemCode

INNER JOIN OITB T3 ON T1.ItmsGrpCod = T3.ItmsGrpCod

INNER JOIN OPLN T4 ON T0.PriceList = T4.ListNum

WHERE T4.[ListName] = 'Selling Prices GBP'

GROUP BY T3.[ItmsGrpNam]

I receive the following error when exectued:

Column 'OITM.ItemCode' is invalid in the select list because it is not contained in either an aggregate function or the GOUP BY clause

Can anyone help?

Thanks in advance

Gail

Edited by: Gail Patterson on Apr 3, 2008 1:32 PM

[Former Member]

Hi,

Please remove the group by function and try it out. Since you are not using any sum function on it.

Thanks

SAGAR

[Former Member]

Hi Sagar

I know the query works without the Group by part but I still need to group the query by Item Group so I can create a report. Or is there another way to group by Item Group when I create a Query Print Layout?

Thanks

Gail

[Former Member]

Hi,

Can u use this query

SELECT T3.ItmsGrpNam, T1.ItemCode, T1.ItemName, T2.OnHand, T2.IsCommited, T4.ListName, T0.Price

FROM ITM1 T0 INNER JOIN

OITM T1 ON T0.ItemCode = T1.ItemCode

INNER JOIN OITW T2 ON T1.ItemCode = T2.ItemCode

INNER JOIN OITB T3 ON T1.ItmsGrpCod = T3.ItmsGrpCod

INNER JOIN OPLN T4 ON T0.PriceList = T4.ListNum

WHERE T4.ListName = 'Selling Prices GBP'

It can help u.

Thanks

SAGAR

[Former Member]

Thanks Sagar

I slelected the ItmsGrpNam as suggested then I just sorted it on this field. Not quite what I was looking for but it does the job it needs to do.

Thanks again for your help.

Gail