Newbie needs help with simple query :-)

Hi All,
I have been trying to learn and use SQL, and have learned quite a bit
(relatively speaking!!). Still, cannot get the below query to work...
if anyone could help out, I would be very grateful (and have learn't
something new!!!) =)
SELECT T0.ItemCode, T0.ItemName,T1.ItmsGrpNam, SUM(T3.OnHand),
SUM(T3.IsCommited), SUM(T3.OnOrder), (SUM(T3.OnHand) + SUM(T3.OnOrder)
- SUM(T3.IsCommited)) AS 'Available', T2.Currency, T2.Price
FROM OITM T0 INNER JOIN OITB T1 ON T0.ItmsGrpCod = T1.ItmsGrpCod INNER
JOIN ITM1 T2 ON T0.ItemCode = T2.ItemCode INNER JOIN OITW T3 ON
T0.ItemCode = T3.ItemCode
WHERE T0.PrchseItem = 'Y' AND T2.PriceList = '[%1]' AND T1.ItmsGrpNam
= '[%2]'
The summing bits are because in OITW there are around 30 warehouses, so
need the "total" on hand etc. Can get it to work with just OITW, but
need the other info in the same query as well :-(
Thanks,
RajivYou'll need to append a GROUP BY clause to the end of the query and any
specify any columns not aggregated in the SELECT list.
HTH
Jerry
"abd08" <dejaonly@.hotmail.com> wrote in message
news:1130260327.349774.100720@.o13g2000cwo.googlegroups.com...
> Hi All,
> I have been trying to learn and use SQL, and have learned quite a bit
> (relatively speaking!!). Still, cannot get the below query to work...
> if anyone could help out, I would be very grateful (and have learn't
> something new!!!) =)
> SELECT T0.ItemCode, T0.ItemName,T1.ItmsGrpNam, SUM(T3.OnHand),
> SUM(T3.IsCommited), SUM(T3.OnOrder), (SUM(T3.OnHand) + SUM(T3.OnOrder)
> - SUM(T3.IsCommited)) AS 'Available', T2.Currency, T2.Price
> FROM OITM T0 INNER JOIN OITB T1 ON T0.ItmsGrpCod = T1.ItmsGrpCod INNER
> JOIN ITM1 T2 ON T0.ItemCode = T2.ItemCode INNER JOIN OITW T3 ON
> T0.ItemCode = T3.ItemCode
> WHERE T0.PrchseItem = 'Y' AND T2.PriceList = '[%1]' AND T1.ItmsGrpNam
> = '[%2]'
> The summing bits are because in OITW there are around 30 warehouses, so
> need the "total" on hand etc. Can get it to work with just OITW, but
> need the other info in the same query as well :-(
> Thanks,
> Rajiv
>|||"abd08" <dejaonly@.hotmail.com> wrote in message
news:1130260327.349774.100720@.o13g2000cwo.googlegroups.com...
> Hi All,
> I have been trying to learn and use SQL, and have learned quite a bit
> (relatively speaking!!). Still, cannot get the below query to work...
> if anyone could help out, I would be very grateful (and have learn't
> something new!!!) =)
> SELECT T0.ItemCode, T0.ItemName,T1.ItmsGrpNam, SUM(T3.OnHand),
> SUM(T3.IsCommited), SUM(T3.OnOrder), (SUM(T3.OnHand) + SUM(T3.OnOrder)
> - SUM(T3.IsCommited)) AS 'Available', T2.Currency, T2.Price
> FROM OITM T0 INNER JOIN OITB T1 ON T0.ItmsGrpCod = T1.ItmsGrpCod INNER
> JOIN ITM1 T2 ON T0.ItemCode = T2.ItemCode INNER JOIN OITW T3 ON
> T0.ItemCode = T3.ItemCode
> WHERE T0.PrchseItem = 'Y' AND T2.PriceList = '[%1]' AND T1.ItmsGrpNam
> = '[%2]'
> The summing bits are because in OITW there are around 30 warehouses, so
> need the "total" on hand etc. Can get it to work with just OITW, but
> need the other info in the same query as well :-(
> Thanks,
> Rajiv
>
Rajiv,
All non-aggregate columns in the SELECT list (i.e. all columns that don't
have a SUM tied to them) must be included in a GROUP BY clause.
Try the following... NOTE: untested
SELECT T0.ItemCode,
T0.ItemName,
T1.ItmsGrpNam,
SUM(T3.OnHand),
SUM(T3.IsCommited),
SUM(T3.OnOrder),
(SUM(T3.OnHand) + SUM(T3.OnOrder) - SUM(T3.IsCommited))
AS 'Available',
T2.Currency,
T2.Price
FROM OITM T0
JOIN OITB T1 ON T0.ItmsGrpCod = T1.ItmsGrpCod
JOIN ITM1 T2 ON T0.ItemCode = T2.ItemCode
JOIN OITW T3 ON T0.ItemCode = T3.ItemCode
WHERE T0.PrchseItem = 'Y'
AND T2.PriceList = '[%1]'
AND T1.ItmsGrpNam = '[%2]'
GROUP BY
T0.ItemCode,
T0.ItemName,
T1.ItmsGrpNam,
T2.Currency,
T2.Price
Rick Sawtell
MCT, MCSD, MCDBA|||Wow... that seemed to work. Hmm... so have to group all non "aggregated
columns". Ok, something new to play with!!!
THANK YOU GUYS!!!! =)
Rajiv