CODEWITHSUNDEEP
sqlsql-servert-sql
CFo
CFo

Reputation: 109

When Selecting a decimal value it is not formatting correctly

I have the table below and when I do a select where I am totaling the Times and Events, one of them is not formatting correctly, even though the temp table is defined as decimal(9,1).

DECLARE @mediaTable TABLE
(
    MediaCode varchar(1),
    NumEvents tinyint,
    TotalTime decimal (9, 1),
    AverageTime decimal (9, 1)
)

Table Data:

MediaCode    NumEvents    TotalTime    AverageTime
A            2            8.0          4.0
B            42           104.0        2.5
C            26           26.2         1.0
D            0            0.0          0.0
E            4            14.0         3.5
F            0            0.0          0.0
G            2            2.5          1.3
H            0            0.0          0.0
I            4            9.0          2.3
J            1            2.0          2.0
L            6            57.5         9.6
M            8            12.0         1.5
N            0            0.0          0.0
P            0            0.0          0.0
S            4            20.0         5.0
T            4            10.0         2.5
V            2            1.5          0.8
W            8            29.0         3.6
X            0            0.0          0.0

The query that I am running is:

SELECT      SUM(NumEvents) AS [sum],
            SUM(TotalTime) AS [sum],
            SUM(CASE WHEN MediaCode IN ('A','B','D','E','F','G','I','J','L','M','P','S','T') THEN NumEvents END) AS [sum], 
            SUM(CASE WHEN MediaCode IN ('A','B','D','E','F','G','I','J','L','M','P','S','T') THEN TotalTime END) AS [sum], 
            SUM(CASE WHEN MediaCode IN ('B', 'T') THEN NumEvents / 2 ELSE NumEvents END) AS [sum],
            SUM(CASE WHEN MediaCode IN ('B', 'T') THEN TotalTime / 2 ELSE TotalTime END) AS [sum] 
    FROM    @mediaTable

The query results are:

sum    sum    sum    sum    sum    sum
113    295.7  77     239.0  90     238.700000

Its that 238.700000 that I can't get to format / display correctly.

The line that is giving me fits is 

SUM(CASE WHEN MediaCode IN ('B', 'T') THEN TotalTime / 2 ELSE TotalTime END) AS [sum]

I've tried formatting, casting, and converting but it doesn't want to cooperate. I know it is probably the /2 that is doing it, but I can't figure out how to get around it. Any Ideas? Any assistance is appreciated.

Upvotes: 1

Views: 73

Answers (2)

Zaynul Abadin Tuhin
Zaynul Abadin Tuhin

Reputation: 32021

CAST() function will work for you that already said in comment

select CAST(238.700000 AS DECIMAL(9,1)) ---it returns 238.7

so in your case it would be 

  CAST(SUM(CASE
    WHEN MediaCode IN ('B', 'T') THEN TotalTime / 2
    ELSE TotalTime
  END) AS decimal(9, 1))

Upvotes: 2

Yogesh Sharma
Yogesh Sharma

Reputation: 50173

If you want to get fix scale then you need to do cast() to define precision & scale with decimal() :

SELECT . . .,
      CAST(SUM(CASE WHEN MediaCode IN ('B', 'T') 
                    THEN TotalTime / 2 ELSE TotalTime 
               END) AS DECIMAL(9, 1) --- will return 238.7
          ) AS [sum]
FROM @mediaTable mt;

Upvotes: 1

Related Questions