Using calculated values within the same Select Statement
-
Is there anyway to create a SQL Statement that would calculate the sum of two values and then use those two summed values to calculate a third value within the same Select Statement? (We have SQL Server 2000, SP3).
SELECT
PriceDate,
PortName,
'Amount1' = (SELECT SUM(BaseSettleAmt) FROM Table1 WHERE TransCatCode In ('005')),
'Amount2' = (SELECT SUM(BaseSettleAmt) FROM Table1 WHERE TransCatCode In ('010')),
'Par' = ( ('Amount1' - 'Amount2') / 1000 )
FROM Table1
WHERE SUBSTRING(PortName, 1, 8) = 'VRSF4595'
AND TransCatCode In ('005', '010')
GROUP BY PriceDate, PortName
Thanks in advance, Kevin
-
Select dtMain.*, (Amount1 + Amount2) / 1000 as Par from (
SELECT
PriceDate,
PortName,
'Amount1' = (SELECT SUM(BaseSettleAmt) FROM Table1 WHERE TransCatCode In ('005')),
'Amount2' = (SELECT SUM(BaseSettleAmt) FROM Table1 WHERE TransCatCode In ('010'))
FROM Table1
WHERE SUBSTRING(PortName, 1, 8) = 'VRSF4595'
AND TransCatCode In ('005', '010')
GROUP BY PriceDate, PortName
) dtMain
-
It is, though you have to move the third sum 'outside' by wrapping the amount1 and amount2 calculations in a derived table.
I think this is what you want..
SELECT x.PriceDate,
x.PortName,
x.Amount1,
x.Amount2,
((x.Amount1 - x.Amount2) / 1000) as 'Par'
FROM (
SELECT
PriceDate,
PortName,
sum(case when TransCatCode = '005' then BaseSettleAmt else 0 end) as 'Amount1',
sum(case when TransCatCode = '010' then BaseSettleAmt else 0 end) as 'Amount2'
FROM Table1
WHERE SUBSTRING(PortName, 1, 8) = 'VRSF4595'
AND TransCatCode In ('005', '010')
GROUP BY PriceDate, PortName
) x
I changed your subselects to CASE statements instead. I don't know your data, but as it is written you're summing BaseSettleAmt for TransCatCodes 005 or 010 that have other starting PortNames than 'VRSF4595'...
/Kenneth
-
Thanks. Can I use the same outer query to get a net of (Amount1 minus Amount2) for all records in the result set?
-
Thanks. Can I use the same outer query to get a net of (Amount1 minus Amount2) for all records in the result set?
-
No... that column doesn't really exist in that query, it has to be processed.
-
SELECT
PriceDate,
PortName,
'Amount1' = SUM(CASE WHEN TransCatCode='005'THEN BaseSettleAmt ELSE 0 END),
'Amount2' = SUM(CASE WHEN TransCatCode='010'THEN BaseSettleAmt ELSE 0 END),
'Par'=SUM(CASE WHEN TransCatCode='005'THEN BaseSettleAmt WHEN TransCatCode='010' THEN -BaseSettleAmt ELSE 0 END)/1000
FROM Table1
WHERE SUBSTRING(PortName, 1, 8) = 'VRSF4595'
AND TransCatCode In ('005', '010')
GROUP BY PriceDate, PortName
Kindest Regards,Vasc
-
Nice... didn't think of that one
.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply