locked
Need to get output as float(eg. 30.00 not 30) for this column RRS feed

  • Question

  • User1182587605 posted

    I have the following query which returns number output for Total but I need a float output for this.

    My query for integer value is :

    select SUM(Qty)as TotalQty, SUM(Qty*Cost)as Total from IC_Inventory_T where Invy_Num = 'INV918'

    I want to convert this into SUM(Qty*Cost)as Total to a float output which sums up values and returns single row Eg: 20706

    Now, I want to change the Query to return float or value like 30.00. I have changed my query to:

    select SUM(Qty)as TotalQty, Cast((Qty*(Cast(Cost as float))) as varchar(10))Total from IC_Inventory_T where Invy_Num = 'INV918'

    But this returns error:

    Msg 8120, Level 16, State 1, Line 3
    Column 'IC_Inventory_T.Qty' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.
    Msg 8120, Level 16, State 1, Line 3
    Column 'IC_Inventory_T.Cost' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.
    

    can anyone please help me. I can not place Group by as the result will change into multiple values I need a single Total for all the Qty and Cost as float in my query.

    Wednesday, November 23, 2016 7:01 AM

Answers

  • User364663285 posted

    Try

    select cast(SUM(Qty) as decimal(14,2)) as TotalQty, cast(SUM(Qty*Cost) as decimal(14,2)) as Total from IC_Inventory_T where Invy_Num = 'INV918'

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Wednesday, November 23, 2016 7:33 AM

All replies

  • User364663285 posted

    Try

    select cast(SUM(Qty) as decimal(14,2)) as TotalQty, cast(SUM(Qty*Cost) as decimal(14,2)) as Total from IC_Inventory_T where Invy_Num = 'INV918'

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Wednesday, November 23, 2016 7:33 AM
  • User1182587605 posted

    But the total which is coming is not accurate though. The value is 29044.00 where it should be 28949.48. The value that returns is not accurate. Can anyone help e in this...

    Wednesday, November 23, 2016 9:08 AM