subset quries - SQL Server Forums

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

.NET Inside SQL Server (2005)

subset quries

Author

Topic

kalpeshpatelbyk
Starting Member

1 Post

Posted - 2008-09-02 : 06:30:09

i had a table name flowers in which there are following fields
table:Tempflower
Flower_Code,Flower_Group,Flower_Qty,FlowerBuy_sell
0001,0002,200,B
0001,0002,200,s
0002,0001,201,B
0002,0001,219,S

now i m trying to get output in following ways in new table

Flower_Code,Flower_Group,NetBuy,NetSell,NetQty
0001 , 0002, 200, 200, 0
0002 , 0001, 201, 219, -18
and so on
i had tried to get answer by
insert into flowertable Select flower_code,Flower_Group,netbuy as sum(select flowerqty from where flowerqty=B),netsell as sum(select flowerqty from where flowerqty=S),netqty (sum(select flowerqty from where flowerqty=B)-sum(select flowerqty from where flowerqty=S),
groupby flower_code,frower_Group

is there any thing wrong in this qurey i m not getting it plz reply

KSP

Ifor
Aged Yak Warrior

700 Posts

Posted - 2008-09-02 : 07:02:28

[code]SELECT *
,NetBuy - NetSell AS NetQty
FROM
(
SELECT Flower_Code, Flower_Group
,SUM(CASE WHEN FlowerBuy_sell = 'B' THEN Flower_Qty END) AS NetBuy
,SUM(CASE WHEN FlowerBuy_sell = 'S' THEN Flower_Qty END) AS NetSell
FROM Tempflower
GROUP BY Flower_Code, Flower_Group
) D
[/code]

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-09-02 : 07:04:53

[code]DECLARE @Sample TABLE
(
Flower_Code CHAR(4) NOT NULL,
Flower_Group CHAR(4) NOT NULL,
Flower_Qty INT NOT NULL,
FlowerBuy_sell CHAR(1) NOT NULL
)

INSERT @Sample
SELECT '0001', '0002', 200, 'B' UNION ALL
SELECT '0001', '0002', 200, 's' UNION ALL
SELECT '0002', '0001', 201, 'B' UNION ALL
SELECT '0002', '0001', 219, 'S'

SELECT Flower_Code,
Flower_Group,
SUM(CASE WHEN FlowerBuy_sell = 'B' THEN Flower_Qty ELSE 0 END) AS NetBuy,
SUM(CASE WHEN FlowerBuy_sell = 's' THEN Flower_Qty ELSE 0 END) AS NetSell
FROM @Sample
GROUP BY Flower_Code,
Flower_Group
ORDER BY Flower_Code,
Flower_Group[/code]

E 12°55'05.63"
N 56°04'39.26"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources