Need Help on Displaying items once

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Need Help on Displaying items once

Author

Topic

f1per
Starting Member

2 Posts

Posted - 2008-09-23 : 23:20:39

--Hello all, i need some help, here is the query:

select a.docnum, a.docdate, a.cardcode, a.cardname, b.dscription,
case when left(b.ItemCode,2) = 'B-' then b.quantity else 0 end as SumDus,
case when left(b.ItemCode,2) = 'L-' then b.quantity else 0 end as SumLusin,
case when left(b.ItemCode,2) = 'FG' then b.quantity else 0 end as SumPcs
from ODLN a inner join DLN1 b on a.docentry = b.docentry
left join OITM c on b.itemcode = c.itemcode
where a.CardCode = 'cat0028' and
b.treetype <> 'I'
order by a.docnum

--the output is

b.dscription SumDus SumLusin SumPcs
item a --------10
item a --------10
item a --------10
item b ---------------10
item b ---------------10

--what i want is :
b.dscription SumDus SumLusin SumPcs
item a 30
item b 20

--Can anyone help me with that query.

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2008-09-23 : 23:26:58

Your query looks different from your expected output. Can you clearly explain?

f1per
Starting Member

2 Posts

Posted - 2008-09-23 : 23:57:03

--ok the query is something like this:

select b.dscription,
case when left(b.ItemCode,2) = 'B-' then b.quantity else 0 end as SumDus,
case when left(b.ItemCode,2) = 'L-' then b.quantity else 0 end as SumLusin,
case when left(b.ItemCode,2) = 'FG' then b.quantity else 0 end as SumPcs
from ODLN a inner join DLN1 b on a.docentry = b.docentry
left join OITM c on b.itemcode = c.itemcode
where a.CardCode = 'cat0028' and
b.treetype <> 'I'

--the output is

b.dscription SumDus SumLusin SumPcs
item a --------10
item a --------10
item a --------10
item b ---------------10
item b ---------------10

--what i want is :
b.dscription SumDus SumLusin SumPcs
item a --------30
item b ----------------20

ranganath
Posting Yak Master

209 Posts

Posted - 2008-09-24 : 00:48:38

hi,

try this

select b.dscription,
Sum(case when left(b.ItemCode,2) = 'B-' then b.quantity else 0 end ) as SumDus,
Sum(case when left(b.ItemCode,2) = 'L-' then b.quantity else 0 end )as SumLusin,
Sum(case when left(b.ItemCode,2) = 'FG' then b.quantity else 0 end )as SumPcs
from ODLN a inner join DLN1 b on a.docentry = b.docentry
left join OITM c on b.itemcode = c.itemcode
where a.CardCode = 'cat0028' and
b.treetype <> 'I'
Group By b.dscription

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-09-24 : 01:39:11

or use PIVOT

SELECT dscription,
[B-] AS SumDus,
[L-] AS SumLusin,
[FG] AS SumPcs
FROM
(
select b.dscription, 
left(b.ItemCode,2) AS ItemCode,b.quantity 
from ODLN a inner join DLN1 b on a.docentry = b.docentry 
left join OITM c on b.itemcode = c.itemcode 
where a.CardCode = 'cat0028' and 
b.treetype <> 'I'
)m
PIVOT (SUM(quantity) FOR ItemCode IN ([B-],[L-],[FG]))p

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources