Lets say I have following table:

And I want to perform a select query in SQL server which sums the value of 'diesel' field. diesel in liter gets multiplied by a conversion factor and the Result should look like this.

I am able to convert diesel from liter to gallons and get the value but i cannot separate diesel in gallons from the table and add it to diesel in liter after conversion.

CodePudding user response：

If you are doing only LITERS and GALLONS then a conditional aggregation should to the trick

Example or dbFiddle

Select fuel
      ,units = 'gallon'
      ,total = sum( case when units='liter' then total * 0.264172 else total end )
 From  YourTable
 Group By fuel

Results

fuel    units   total
cng     gallon  50.000000
diesel  gallon  32.641720
lpg     gallon  20.000000

EDIT JUST FOR FUN. Let's add Barrels as well

Declare @YourTable Table ([fuel] varchar(50),[units] varchar(50),[total] int)
Insert Into @YourTable Values 
 ('diesel','gallon',30)
,('lpg','gallon',20)
,('cng','gallon',50)
,('diesel','liter',10)
,('diesel','barrel',1)
 
Select fuel
      ,units = 'gallon'
      ,total = sum( case units when 'liter' then 0.264172 
                               when 'barrel' then 42
                         else 1 
                     end * Total )
 From  @YourTable
 Group By fuel

2nd Results

fuel    units   total
cng     gallon  50.000000
diesel  gallon  74.641720
lpg     gallon  20.000000