CODEWITHSUNDEEP
selectdecimalroundingsql-functionmultiplication
Ashton Sheets
Ashton Sheets

Reputation: 513

SQL multiply and round in select statement

In part of my select statement, I want to round the value to two decimals. The select has a multiplication where I take one column's value and multiply it by another and divide by 100. I want this value to be returned with only two decimal places but nothing seems to be working right. Here's what I've tried so far:

 SELECT
     a.Rate AS 'Rate'
     ,a.LoI AS 'Liability'
 ---------------------------------------------------
 ---- Tried Each Of These Seperately ---------------
 ---------------------------------------------------

     CAST(a.Rate * a.LoI / 100, money) AS 'Premium'

     ROUND(a.Rate * a.LoI / 100,2) AS 'Premium'

     CAST((A.Rate * a.LoI / 100), money) AS 'Premium'

     CONVERT(A.Rate * a.LoI / 100, money) AS 'Premium'

FROM tblAspecs a

a.Rate is a of data type Decimal(10,2)

a.LoI is of data type bigint

I'm Using Microsoft SQL Server Management Studio 2010.

Upvotes: 1

Views: 23167

Answers (1)

KOT
KOT

Reputation: 2126

select cast((123.456 * 2 / 100) as decimal(38,2))

works for me :) But that maybe wasn't what you wanted?

So I guess you could try:

SELECT
 a.Rate AS 'Rate'
 ,a.LoI AS 'Liability'
 ,CAST((a.Rate * a.LoI / 100) AS decimal(38,2)) 'Premium' FROM tblAspecs a

Upvotes: 4

Related Questions