displaying decimals in calculated columns

Question

I have the following code that works, but I want the 'pcttotalorders' column to have 4 decimal places. enter image description here

select
    vendorno as 'Vendor', count (*) as 'Number Of Orders', count (*)*100/(
                                                                            select
                                                                                count(*)
                                                                            from
                                                                                mas_tfi.dbo.ap_invoicehistoryheader
                                                                                                                    ) as pcttotalorders
from
    mas_tfi.dbo.ap_invoicehistoryheader
group by
    vendorno
order by
    'Number of Orders' desc;

Possible duplicate of [Write a number with two decimal places SQL server](https://stackoverflow.com/questions/441600/write-a-number-with-two-decimal-places-sql-server) — Laura, Aug 02 '18 at 21:26

score 2 · Accepted Answer · answered Jul 16 '18 at 21:45

This seems like a duplicate of this question: Write a number with two decimal places SQL server Just change the parameters of the decimal function so the column in the select column would look like--

CONVERT(
        DECIMAL(10,4), 
        (COUNT(*)*100/(SELECT COUNT(*) FROM mas_tfi.dbo.ap_invoicehistoryheader))                                                                                                                   
        ) AS pcttotalorders

score 1 · Answer 2 · answered Jul 16 '18 at 20:46

Try SQL server format function

Select
vendorno as 'Vendor', count (*) as 'Number Of Orders',   
 Format(count (*)*100/(
                                                                        select
                                                                            count(*)
                                                                        from

mas_tfi.dbo.ap_invoicehistoryheader
                                                                                                                ), 
"##.####") as pcttotalorders
from
mas_tfi.dbo.ap_invoicehistoryheader
Group by
vendorno
order by
'Number of Orders' desc;

Reference https://learn.microsoft.com/en-us/sql/t-sql/functions/format-transact-sql?view=sql-server-2017

This will produce the desired formatting, but I think it's worth pointing out that the `format` function returns an `nvarchar` rather than a numeric value, which may lead to unexpected behavior if the OP were to try sorting on these values or using them for further calculations. I think Laura's approach with `convert` is probably better in most cases. — Joe Farrell, Jul 16 '18 at 22:02

displaying decimals in calculated columns

2 Answers2