| 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" |
|
|
|
|
|
|
subset quries