TSQL Divide return Zero

Question

I can't figure out what I'm doing wrong here, why does the divide return zero?

SELECT
    SL.SALESID
  , SUM(CASE WHEN SL.SALESSTATUS = 3 THEN 1 ELSE 0 END)                                      AS 'LinesShipped'
  , COUNT(SL.LINENUM)                                                                        AS 'TotalLines'
  , CAST(SUM(CASE WHEN SL.SALESSTATUS = 3 THEN 1 ELSE 0 END) / COUNT(SL.LINENUM) AS decimal) AS 'ShippedFull'
FROM bmssa.SALESLINE     SL
LEFT OUTER JOIN PickList PL ON SL.INVENTTRANSID = PL.INVENTTRANSID AND SL.DATAAREAID = PL.DATAAREAID
WHERE SL.DATAAREAID = 'ppg' AND SL.SALESID >= 'S2378391' AND SL.SALESID <= 'S2378955'
GROUP BY SL.SALESID
ORDER BY SL.SALESID;

Does this answer your question? [How to get a float result by dividing two integer values using T-SQL?](https://stackoverflow.com/questions/11719044/how-to-get-a-float-result-by-dividing-two-integer-values-using-t-sql). The actual reason is that integer/integer in T-SQL gives an integer. — Andrew Morton, Mar 05 '20 at 16:07

score 1 · Answer 1 · answered Mar 05 '20 at 16:10

1

You are facing integer division. One way to solve it is to force the conversion to a decimal on one of the operands.

This should be enough:

SUM(CASE WHEN SL.SALESSTATUS = 3 THEN 1.0 ELSE 0 END) 
    / COUNT(SL.LINENUM) AS 'ShippedFull'

answered Mar 05 '20 at 16:10

GMB

216,147
25
84
135

score 0 · Answer 2 · answered Mar 05 '20 at 16:11

SELECT
    SL.SALESID
  , SUM(CASE WHEN SL.SALESSTATUS = 3 THEN 1 ELSE 0 END)                                                                 AS 'LinesShipped'
  , COUNT(SL.LINENUM)                                                                                                   AS 'TotalLines'
  , FORMAT(
        CAST(SUM(CASE WHEN SL.SALESSTATUS = 3 THEN 1 ELSE 0 END) AS decimal) / CAST(COUNT(SL.LINENUM) AS decimal), 'p') AS 'ShippedFull'
FROM bmssa.SALESLINE     SL
LEFT OUTER JOIN PickList PL ON SL.INVENTTRANSID = PL.INVENTTRANSID AND SL.DATAAREAID = PL.DATAAREAID
WHERE SL.DATAAREAID = 'ppg' AND SL.SALESID >= 'S2378391' AND SL.SALESID <= 'S2378955'
GROUP BY SL.SALESID
ORDER BY SL.SALESID;

`AS decimal`? *Always* declare you lengths, precisions and scales. — Thom A, Mar 05 '20 at 16:21

TSQL Divide return Zero

2 Answers2