December 4, 2003 at 5:09 pm
Hi,
I need to sum some columns of tinyints. These columns contain 1 or 0.
My Query
SET DATEFORMAT DMY
SELECT
T.MRN,
SUM(T.ADMIT) AS ADMITS,
SUM(T.EMERGENCY) AS EMEGENCIES,
SUM(T.OUTPATIENT) AS OUTPATIENTS,
SUM(T.ADMIT+T.EMERGENCY+T.OUTPATIENT ) AS TOTAL
FROM
TRANSACTIONS T
WHERE
T.TRANSACTION_DTTM BETWEEN CONVERT(DATETIME , '01-08-2003') AND CONVERT(DATETIME , '30-11-2003') AND
(
T.HEORG_REFNO = 11 OR
T.HEORG_REFNO = 394 OR
T.HEORG_REFNO = 401
) AND
(
T.TRAN_TYPE = 'ADM' OR
T.TRAN_TYPE = 'OUT' OR
T.TRAN_TYPE = 'EMD'
)
GROUP BY
T.MRN
ORDER BY
SUM(T.ADMIT+T.EMERGENCY+T.OUTPATIENT) DESC
The result set it returns is in error. As an example, the first line of the resultset is
999999999, 71,1,0, 51
which means that for the MRN 999999999 there were 71 admissions, 1 emergency visit and 0 Outpatient visits for a total of 51 visits. Hang on.... 71+1+0 is 72 and NOT 51 as this query returns!!!
A you can see, this is a problem.
I have tried using sum(convert(integer, <fieldname>)) with no result.
Can anyone shed some light on this odd situation?
Thanks!
December 4, 2003 at 5:37 pm
How embarrasing!
The Outpatient column containd some NULL's.
Of Course 1+1+NULL is NULL.
Thanks,
December 5, 2003 at 5:42 am
Easy to miss. Did you fix the data, or use an IsNull in the sum?
Andy
December 7, 2003 at 2:03 pm
Hi,
I fixed the data and ensured it wouldn't happen again by setting a default on the field (0).
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply