Happy Business Starts Here

Rounding & Truncating to a specific # of decimal places (eg. currency)

Highlighted
Zuora Staff

Rounding & Truncating to a specific # of decimal places (eg. currency)

I'm attempting to include a few currency numbers in my results, but they are returning ridiculously long decimals, so I'm trying to round to 2 decimals and drop the 0s behind that. I've tried different versions of ROUND, CAST, TRUNCATE, and DECLARE but without much success. Here's the SELECT statement I started with, along with the results. Any ideas about how to resolve this would be appreciated! 

SELECT account.accountnumber, account.name, ROUND(SUM(ordertcb.value), 2) booked, ROUND(SUM(invoice.amount), 2) billed, ROUND(account.balance, 2) balance
image.png
 
 
1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Zuora Staff

Re: Rounding & Truncating to a specific # of decimal places (eg. currency)

SELECT account.accountnumber, account.name, CAST(ROUND(SUM(ordertcb.value), 2) as DECIMAL(10,2)) booked, CAST(ROUND(SUM(invoice.amount), 2) as DECIMAL(10,2)) billed, CAST(ROUND(account.balance, 2) as DECIMAL(10,2)) balance
Hey Allyson, thanks for posting! Try something like the sql I added here ^ Casting it to Decimal(10,2) will truncate the remaining 0s you don't want to see. 

View solution in original post

1 REPLY 1
Highlighted
Zuora Staff

Re: Rounding & Truncating to a specific # of decimal places (eg. currency)

SELECT account.accountnumber, account.name, CAST(ROUND(SUM(ordertcb.value), 2) as DECIMAL(10,2)) booked, CAST(ROUND(SUM(invoice.amount), 2) as DECIMAL(10,2)) billed, CAST(ROUND(account.balance, 2) as DECIMAL(10,2)) balance
Hey Allyson, thanks for posting! Try something like the sql I added here ^ Casting it to Decimal(10,2) will truncate the remaining 0s you don't want to see. 

View solution in original post