CODEWITHSUNDEEP
sqlprecision
Boognish
Boognish

Reputation: 103

How to set precision on calculated field

I joined four tables so I could calculate the total price of a sales transaction. The code that I used to join all four tables is also listed below. My issue is that it is calculating a result that has a precision of four decimal places but I would like it to only have a precious of two.

select ol.order_number, co.customer_id, ol.product_id, ol.product_quantity, ol.product_price, sum(ol.product_quantity * ol.product_price) as "Net Total",
zc.sales_tax_applied as "Sales Tax", (sum(ol.product_quantity * ol.product_price) + (sum(ol.product_quantity * ol.product_price) * zc.sales_tax_applied)) as "Total Price"
from order_line ol inner join customer_order co
on ol.order_number = co.order_number
inner join customer c
on co.customer_id = c.customer_id
inner join zipcode zc
on c.zipcode = zc.zipcode
group by ol.order_number, co.customer_id, ol.product_id, ol.product_quantity, ol.product_price, zc.sales_tax_applied;

So, the result is: enter image description here

How can I make the result have a precision of only two decimal places instead of four? Thank you

Upvotes: 1

Views: 135

Answers (1)

shrek
shrek

Reputation: 887

Use round(column_name, 2) as follows - 

select ol.order_number, co.customer_id, ol.product_id, ol.product_quantity, round(ol.product_price,2) as product_price, round(sum(ol.product_quantity * ol.product_price),2) as "Net Total",
round(zc.sales_tax_applied,2) as "Sales Tax", round((sum(ol.product_quantity * ol.product_price) + (sum(ol.product_quantity * ol.product_price) * zc.sales_tax_applied)),2) as "Total Price"
from order_line ol inner join customer_order co
on ol.order_number = co.order_number
inner join customer c
on co.customer_id = c.customer_id
inner join zipcode zc
on c.zipcode = zc.zipcode
group by ol.order_number, co.customer_id, ol.product_id, ol.product_quantity, ol.product_price, zc.sales_tax_applied;

Upvotes: 2

Related Questions