CODEWITHSUNDEEP
sqlsql-server-2005
user1086159
user1086159

Reputation: 1055

Trouble with SQL decimals

I have run a SQL query in SQL Server Management Studio 2005 and when my results come through my Total_Cost column is not formatted as I want.

I have tried adding in a Declare @TOTAL_COST decimal(12,2) clause but cannot seem to get this to work so have taken it out again.

My query is:-

SELECT     
   vwREP_GP_ContractProjectPurchaseCostCatID.PACOSTCATID AS COST_CAT_CODE, 
   vwREP_GP_ContractProjectPurchaseCostCatID.PACOSTCATNME AS COST_CAT_NAME, 
   vwREP_GP_ContractProjectPurchaseRpt.COST, 
   vwREP_GP_ContractProjectPurchaseRpt.ITEMDESC AS ITEM, 
   vwREP_GP_ContractProjectPurchaseRpt.CREDITOR, 
   vwREP_GP_ContractProjectPurchaseRpt.DOC_NUM AS DOCUMENT_NUMBER, 
   vwREP_GP_ContractProjectPurchaseRpt.DESCRIPTION, 
   vwREP_GP_ContractProjectPurchaseRpt.DOC_DATE AS DATE,  
   vwREP_GP_ContractProjectPurchaseRpt.PROJECT AS JOB_CODE_STAGE, 
   vwREP_GP_ContractProjectPurchaseRpt.COMPOUNDNAME AS CATEGORY_DESCRIPTION
FROM         
   vwREP_GP_ContractProjectPurchaseCostCatID 
INNER JOIN
   vwREP_GP_ContractProjectPurchaseRpt ON vwREP_GP_ContractProjectPurchaseCostCatID.PACOSTCATID = vwREP_GP_ContractProjectPurchaseRpt.PACOSTCATID

SELECT  
   CATEGORY_DESCRIPTION, SUM(COST) AS TOTAL_COST 
FROM    
   (SELECT 
        COMPOUNDNAME AS CATEGORY_DESCRIPTION, COST 
    FROM   
        vwREP_GP_ContractProjectPurchaseRpt 
   ) A
GROUP BY 
    CATEGORY_DESCRIPTION

This gives me the results I am looking for but the Total_Cost produces numbers in a 5 decimal format.

For example:

156713.55000

What is the best way to get the result to read something along the lines of - 

156713.55

Thanks in advance

Upvotes: 2

Views: 124

Answers (2)

Chris Gessler
Chris Gessler

Reputation: 23113

If you only store 2 decimal places, then I wouldn't worry about it. Almost every language I'm aware of will throw away the extra zeros (except SQL) :)

On the other hand, if you ARE storing more than 2 decimals (i.e. 123.456), then I would use round() instead for accuracy.

 ...

SELECT  CATEGORY_DESCRIPTION, round(SUM(COST), 2) AS TOTAL_COST  

FROM    (   
        SELECT COMPOUNDNAME AS CATEGORY_DESCRIPTION, COST  
        FROM   vwREP_GP_ContractProjectPurchaseRpt  
        ) A 

GROUP BY  
        CATEGORY_DESCRIPTION

Upvotes: 0

Quassnoi
Quassnoi

Reputation: 425251

SELECT     vwREP_GP_ContractProjectPurchaseCostCatID.PACOSTCATID AS COST_CAT_CODE, 
           vwREP_GP_ContractProjectPurchaseCostCatID.PACOSTCATNME AS COST_CAT_NAME, 
           vwREP_GP_ContractProjectPurchaseRpt.COST, 
           vwREP_GP_ContractProjectPurchaseRpt.ITEMDESC AS ITEM, 
           vwREP_GP_ContractProjectPurchaseRpt.CREDITOR, 
           vwREP_GP_ContractProjectPurchaseRpt.DOC_NUM AS DOCUMENT_NUMBER, 
           vwREP_GP_ContractProjectPurchaseRpt.DESCRIPTION, 
           vwREP_GP_ContractProjectPurchaseRpt.DOC_DATE AS DATE,  
           vwREP_GP_ContractProjectPurchaseRpt.PROJECT AS JOB_CODE_STAGE, 
           vwREP_GP_ContractProjectPurchaseRpt.COMPOUNDNAME AS CATEGORY_DESCRIPTION


FROM         vwREP_GP_ContractProjectPurchaseCostCatID INNER JOIN
                      vwREP_GP_ContractProjectPurchaseRpt ON vwREP_GP_ContractProjectPurchaseCostCatID.PACOSTCATID = vwREP_GP_ContractProjectPurchaseRpt.PACOSTCATID



SELECT  CATEGORY_DESCRIPTION, CAST(SUM(COST) AS DECIMAL(12, 2)) AS TOTAL_COST

FROM    (  
        SELECT COMPOUNDNAME AS CATEGORY_DESCRIPTION, COST 
        FROM   vwREP_GP_ContractProjectPurchaseRpt 
        ) A
GROUP BY 
        CATEGORY_DESCRIPTION

Upvotes: 1

Related Questions