August 18, 2010 at 9:36 am
Hi all,
I was trying to write query to use 2 columns balance and amount in this code, and get balance where myorder =1 and sum of all amounts as final amount.
Can not figure out how to join these two different criterias in one..
myorder is generated using my CTE
sample code
CREATE TABLE [abc](
[ac] [varchar](14) NULL,
[bal] [varchar](14) NULL,
[amt] [varchar](14) NULL,
[my_order] [varchar](14) NULL
)
INSERT INTO abc ( ac,bal,amt,my_order) VALUES ( '780007', '214.51', '0.00', '1')
INSERT INTO abc ( ac,bal,amt,my_order) VALUES ( '780007', '708.81', '7.00', '2')
INSERT INTO abc ( ac,bal,amt,my_order) VALUES ( '780007', '308.81', '15.00', '3')
OUTPUT should be:
780007 214.51 22
any help
Thanks [/font]
August 18, 2010 at 9:41 am
Try this:
; WITH CTE AS
(
SELECT sum (amt) [SUM] FROM [abc]
)
SELECT T1.ac,T1.bal , T2.[SUM]
FROM [abc] T1
CROSS JOIN CTE T2
WHERE T1.[my_order] = 1
~Edit : Fixed the Table aliases
August 18, 2010 at 9:50 am
Thanks a lot ..it works....
It works for one account , but I will have more in the table, then it takes sum of all and applies same amount to all account...
changed it a little
; WITH CTE AS
(
SELECT ac,sum ( AMT ) [SUM] FROM [abc]
group by ac
)
SELECT T1.ac,T1.bal , T2.[SUM]
FROM [abc] T1
JOIN CTE T2
on T1.ac = T2.ac
WHERE
T1.my_order = 1
Thanks [/font]
August 18, 2010 at 10:21 am
If you want it to be faster (less scans and less reads) do:
select ac
, SUM(CASE WHEN my_order = 1 THEN bal ELSE 0 END) bal
, SUM(amt) [SUM]
from abc
group by ac
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply