May 17, 2016 at 9:06 am
Hi all,
I need to divide two function columns but can't figure out how to do it:-
SELECT *
(A)SUM(J.JourneyDistanceMiles),
(B)SUM(J.JourneyTotalFuelUsed)*0.219969157
(NEW COLUMN) A/B AS MPG
FROM tablename
Hope the above makes sense in terms of what i'm trying to do?
May 17, 2016 at 9:17 am
How about this?
😎
SELECT
*
,(A)SUM(J.JourneyDistanceMiles)
,(B)SUM(J.JourneyTotalFuelUsed)*0.219969157
,(A)SUM(J.JourneyDistanceMiles) / ((B)SUM(J.JourneyTotalFuelUsed)*0.219969157) as [(NEW COLUMN) A/B AS MPG]
FROM tablename
May 17, 2016 at 9:19 am
One option is to do it with CTE. In the CTE you define the 2 columns, and then in the select statement that uses the CTE you can use those names. Bellow is an example.
[Code]
with MyCTE as (
SELECT (select count(*) from sys.tables) as TCount,
(select count(*) from sys.procedures) as PCount
)
select TCount, PCount, TCount + PCount as TotalCount
from MyCTE
[/Code]
Adi
--------------------------------------------------------------
To know how to ask questions and increase the chances of getting asnwers:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
May 17, 2016 at 9:25 am
This gives me a divide by zero error? (in reply to Eirikur Eiriksson)
I assume i need to remove the bits in brackets so my statement reads:-
SUM(J.JourneyDistanceMiles) AS Mileage,
SUM(J.JourneyTotalFuelUsed)*0.219969157 AS Fuel,
SUM(J.JourneyDistanceMiles) / (SUM(J.JourneyTotalFuelUsed)*0.219969157) as [SUM(J.JourneyDistanceMiles)/SUM(J.JourneyTotalFuelUsed)*0.219969157) AS MPG]
Are the aliases causing the error?
May 17, 2016 at 9:29 am
Then force NULL output if the divisor is 0
😎
SELECT
*
,(A)SUM(J.JourneyDistanceMiles)
,(B)SUM(J.JourneyTotalFuelUsed)*0.219969157
,(A)SUM(J.JourneyDistanceMiles) / NULLIF(((B)SUM(J.JourneyTotalFuelUsed)*0.219969157),0) as [(NEW COLUMN) A/B AS MPG]
FROM tablename
May 17, 2016 at 9:30 am
In which case, can I borrow your car, please? It doesn't appear to have used any fuel on any of its journeys!
John
May 17, 2016 at 9:41 am
Perfect thanks.
One last bit, can I give an alias of 'MPG' to the calculation part?
SUM(J.JourneyDistanceMiles) AS Mileage,
SUM(J.JourneyTotalFuelUsed)*0.219969157 AS Fuel,
SUM(J.JourneyDistanceMiles) / NULLIF((SUM(J.JourneyTotalFuelUsed)*0.219969157),0) AS [SUM(J.JourneyDistanceMiles)/SUM(J.JourneyTotalFuelUsed)*0.219969157 AS MPG]
May 17, 2016 at 9:43 am
If the divisor is zero then obviously the division is invalid, next thing for you is to check your data
😎
May 17, 2016 at 9:46 am
SELECT
*
,(A)SUM(J.JourneyDistanceMiles)
,(B)SUM(J.JourneyTotalFuelUsed)*0.219969157
,(A)SUM(J.JourneyDistanceMiles) / NULLIF(((B)SUM(J.JourneyTotalFuelUsed)*0.219969157),0) as [MPG]
FROM tablename;
😎
May 17, 2016 at 9:52 am
Perfect - thank you.
Your help is greatly appreciated.
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply