CODEWITHSUNDEEP
sqlt-sql
Palendrone
Palendrone

Reputation: 363

T-SQL Sum Case Confusion

I am currently doing a SUM CASE to workout a percentage, however the entire string returns zero's or ones (integers) but I don't know why. I have written the SQL in parts to break it out and ensure the underlying data is correct which it is, however when I add the last part on to do the percentage it fails. Am I missing something?

SELECT 
    SUPPLIERCODE,
    (SUM(CASE WHEN ISNULL(DATESUBMITTED,0) - ISNULL(FAILDATE,0) <15 THEN 1 ELSE 0 END)) AS     ACCEPTABLE,
    COUNT(ID) AS TOTALSUBMITTED,
    (SUM(CASE WHEN ISNULL(DATESUBMITTED,0) - ISNULL(FAILDATE,0) <15 THEN 1 ELSE 0 END)/COUNT(ID))
FROM SUPPLIERDATA
GROUP BY SUPPLIERCODE

For example here's some of the data returned:

SUPPLIERCODE    ACCEPTABLE  TOTALSUBMITTED  Column1
HBFDE2          1018        1045            0
DTETY1          4           4               1
SWYTR2          579         736             0
VFTEQ3          2104        2438            0

I know I could leave the other columns and use an excel calculation but I'd rather not... Any help would be well received. Thanks

Upvotes: 0

Views: 128

Answers (3)

L&#225;szl&#243; Koller
L&#225;szl&#243; Koller

Reputation: 1159

All you have to do is avoid integer division by giving your database engine a hint.

In SQL Server, you would use:

SELECT 
    SUPPLIERCODE,
    (SUM(CASE WHEN ISNULL(DATESUBMITTED, 0) - ISNULL(FAILDATE, 0) < 15 THEN 1 ELSE 0 END)) AS ACCEPTABLE,
    COUNT(ID) AS TOTALSUBMITTED,
    (SUM(CASE WHEN ISNULL(DATESUBMITTED, 0) - ISNULL(FAILDATE, 0) < 15 THEN 1 ELSE 0 END) / (COUNT(ID) * 1.0))
FROM
    SUPPLIERDATA
GROUP BY
    SUPPLIERCODE

Upvotes: 0

Martin
Martin

Reputation: 16423

This is due to the fact that SQL Server is treating your values as INTs for the purpose of division. Try the following and you will see the answer 0:

PRINT 1018 / 1045

In order to allow your operation to work correctly, you need to convert your values to FLOATs, like so:

PRINT CAST(1018 AS FLOAT) / 1045

This will produce the answer 0.974163 as expected.

A simple change to your statement to introduce a cast to FLOAT will sort your problem:

SELECT 
    SUPPLIERCODE,
    (SUM(CASE WHEN ISNULL(DATESUBMITTED,0) - ISNULL(FAILDATE,0) <15 THEN 1 ELSE 0 END)) AS     ACCEPTABLE,
    COUNT(ID) AS TOTALSUBMITTED,
    (CAST(SUM(CASE WHEN ISNULL(DATESUBMITTED,0) - ISNULL(FAILDATE,0) <15 THEN 1 ELSE 0 END) AS FLOAT) / COUNT(ID))
FROM SUPPLIERDATA
GROUP BY SUPPLIERCODE

Upvotes: 1

MikkaRin
MikkaRin

Reputation: 3084

SELECT 
    SUPPLIERCODE,
    (SUM(CASE WHEN ISNULL(DATESUBMITTED,0) - ISNULL(FAILDATE,0) <15 THEN 1 ELSE 0 END)) AS     ACCEPTABLE,
    COUNT(ID) AS TOTALSUBMITTED,
    (SUM(CASE WHEN ISNULL(DATESUBMITTED,0) - ISNULL(FAILDATE,0) <15 THEN 1 ELSE 0 END)*1.0/COUNT(ID))
FROM SUPPLIERDATA
GROUP BY SUPPLIERCODE

You need convert your result to float. It can be easy done by multiply on 1.0

Upvotes: 2

Related Questions