How can I Roundoff in SQL with a sum function-CodePudding

In My SQL Code I am trying to round the value to 2 decimal point with sum

select ((SUM(Round((CAST(PE.GstTotal as float) * PE.Quantity) / 2 ),2))) FROM [dbo].[PharmacyEntry] PE

But I am getting an error. Could someone correct me on this. Error

CodePudding user response：

It's sometimes helpful to vertically align all your parenthesis pairs to see where you've got one wrong:

    select 
    (
      (
        SUM
        (
          Round
          (
            (
              CAST
                (
                   PE.GstTotal as float
                ) 
                * 
                PE.Quantity
            ) 
            /
            2 
          ),
          2
        )
      )
    ) 

FROM [dbo].[PharmacyEntry] PE

CodePudding user response：

You're providing 2 as a second parameter to sum instead of round. Try this:

select SUM(Round((CAST(PE.GstTotal as float) * PE.Quantity) / 2 , 2)) 
FROM [dbo].[PharmacyEntry] PE