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

newbie - Stored procedure

I have simple question - could be a bit stupid
I created a form in which several fields are obligatory, some are free to
fill in
now what is the best practice to follow
Should i create several SP's for every possible combination ?
Should i Create one SP where variables are possibly empty (if that is the
best thing to do, what's the right syntax ?)
Or should I create one SP with the obligatory values, and afterwards search
through the resultset?
thanx in advance...One proc should do it. You can have parameters with default values, if you
so choose:
create proc MyProc
(
@.id int
, @.x char (5) = 'ALFKI'
)
as
...
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com
.
"Boonaap" <Boonaap@.discussions.microsoft.com> wrote in message
news:415B2831-6626-457A-98D4-42ED64F2C7F7@.microsoft.com...
I have simple question - could be a bit stupid
I created a form in which several fields are obligatory, some are free to
fill in
now what is the best practice to follow
Should i create several SP's for every possible combination ?
Should i Create one SP where variables are possibly empty (if that is the
best thing to do, what's the right syntax ?)
Or should I create one SP with the obligatory values, and afterwards search
through the resultset?
thanx in advance...