February 3, 2010 at 11:27 pm
Table: ITMLED
Recsno itmcod opeqty inwqty isuqty
6200 001 500 NULL NULL
442 001 NULL NULL 100
22 001 NULL 700 NULL
23 001 NULL 500 NULL
443 001 NULL NULL 400
How do I get itmcod wise result in 1 row like
ITMCOD,OPNQTY,INWQTY,ISUQTY,((OPNQTY+ INWQT)-ISUQTY)AS CLOSQTY
001 500 1200 500 1200
From above data in ITMLED.
Keyur Gohel
February 3, 2010 at 11:36 pm
KeyurGohel (2/3/2010)
Table: ITMLEDRecsno itmcod opeqty inwqty isuqty
6200 001 500 NULL NULL
442 001 NULL NULL 100
22 001 NULL 700 NULL
23 001 NULL 500 NULL
443 001 NULL NULL 400
How do I get itmcod wise result in 1 row like
ITMCOD,OPNQTY,INWQTY,ISUQTY,((OPNQTY+ INWQT)-ISUQTY)AS CLOSQTY
001 500 1200 500 1200
From above data in ITMLED.
You pretty much have it already
Select TMCOD,OPNQTY,INWQTY,ISUQTY,((OPNQTY+ INWQT)-ISUQTY)AS CLOSQTY
From itmcode
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
February 3, 2010 at 11:51 pm
KeyurGohel (2/3/2010)
ITMCOD,OPNQTY,INWQTY,ISUQTY,((OPNQTY+ INWQT)-ISUQTY)AS CLOSQTY001 500 1200 500 1200
select itmcod,
sum(opeqty)as opeqty,
sum(inwqty)as inwqty,
sum(isuqty) as isuqty,
((sum(opeqty)+ sum(inwqty))-sum(isuqty))AS CLOSQTY
from ITMLED
group by itmcod
February 3, 2010 at 11:56 pm
I may have misunderstood your first post.
If you want an aggregate of all of the records in the table then:
select itmcod,
sum(isnull(opeqty,0))as opeqty,
sum(isnull(inwqty,0))as inwqty,
sum(isnull(isuqty,0)) as isuqty,
((sum(isnull(opeqty,0))+ sum(isnull(inwqty,0)))-sum(isnull(isuqty,0)))AS CLOSQTY
from ITMLED
group by itmcod
Will get you the correct results. Since you have null values in your dataset, you must account for them in your aggregations.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply