Getting two counts and then dividing them

Question

I am attempting to get two counts and then divide those two counts to get the ratio of the items I am counting. I saw this post here and tried that. I am getting an error in my results, no error message just incorrect number. I am using SQL-Server 2008

Here is my code:

-- INTERNAL PEPPER REPORT
--#####################################################################

-- VARIABLE DECLARATION AND INITIALIZATION
DECLARE @SD DATETIME
DECLARE @ED DATETIME

SET @SD = '2013-01-01'
SET @ED = '2013-03-31'

-- TABLE DECLARATION ##################################################
DECLARE @TABLE1 TABLE(NUMERATOR INT, DENOMINATOR INT, RATIO INT)
--#####################################################################

-- WHAT GETS INSERTED INTO TABLE 1
INSERT INTO @TABLE1
SELECT
A.NUM, A.DENOM, A.NUM/A.DENOM 

FROM
(
-- COLUMN SELECTION. TWO NUMBERS WILL REPRESENT A NUM AND A DENOM
SELECT 
    (SELECT COUNT(DRG_NO)
        FROM smsdss.BMH_PLM_PtAcct_V
        WHERE drg_no IN (061,062,063,064,065,066)
        AND Adm_Date BETWEEN @SD AND @ED
        AND PLM_PT_ACCT_TYPE = 'I')
        AS NUM,
    (SELECT COUNT(DRG_NO)
        FROM smsdss.BMH_PLM_PtAcct_V
        WHERE drg_no IN (061,062,063,064,065,066,067,068,069)
        AND Adm_Date BETWEEN @SD AND @ED
        AND Plm_Pt_Acct_Type = 'I')
        AS DENOM
)A

SELECT NUMERATOR, DENOMINATOR, RATIO
FROM @TABLE1

The counts get produced and displayed correctly, but for a ratio I get 0 and am not sure as to why I get this.

Thank You,

score 19 · Accepted Answer · answered Jun 11 '13 at 18:33

19

Use SELECT A.NUM, A.DENOM, cast(A.NUM as float)/cast(A.DENOM as float)

SQL Server consider that A.NUM / A.DENOM are int, because A.NUM and A.DENUM are int

answered Jun 11 '13 at 18:33

VahiD

1,014
1
14
30

This still produced a 0...I am going to try and change inthe table creation instead of INT to FLOAT. – MCP_infiltrator Jun 11 '13 at 18:37
1

Count always returns INT so whetever you do in table, nothing changes in return type of Count(), I tested this query and it's worked perfect – VahiD Jun 24 '13 at 08:35
1

learn something new everyday, I didn't know that COUNT() returns INT – MCP_infiltrator Jun 24 '13 at 12:24

score 4 · Answer 2 · answered Jun 11 '13 at 19:16

4

The structure of your query bothers me. You can do it much more efficiently as:

SELECT A.NUMer, A.DENOM, cast(A.NUMer as float)/A.DENOM 
FROM (SELECT COUNT(case when drg_no IN (061,062,063,064,065,066) then DRG_NO
                   end ) as Numer,
             count(case when drg_no IN 061,062,063,064,065,066,067,068,069) then DRG_NO
                   end) as denom
      FROM smsdss.BMH_PLM_PtAcct_V
      WHERE drg_no IN (061,062,063,064,065,066)
        AND Adm_Date BETWEEN @SD AND @ED
        AND PLM_PT_ACCT_TYPE = 'I'
     ) a

This doesn't affect the integer divide issue, but your original query is overcomplicated.

answered Jun 11 '13 at 19:16

Gordon Linoff

1,242,037
58
646
786

Thank you for sharing this as it bothers me as well, I just do not know how to structure it better, also I have to scale it out, meaning I have to get many more Numerators, Denominators and Ratios all inside the same query. Should I post what I draw up as my final solution and ask to see how it can be made more efficient? – MCP_infiltrator Jun 12 '13 at 12:05
Alos your query spead up getting the information from 2.5 minutes down to 0.5 minutes – MCP_infiltrator Jun 12 '13 at 12:12

score 1 · Answer 3 · answered Jun 11 '13 at 18:30

1

The ratio of two integers will be an integer. For example: 10/20 = 0.5 = 0. You need to cast your ratio into a float in order to get an accurate answer.

answered Jun 11 '13 at 18:30

SOfanatic

5,523
5
36
57

Thank you very much. But even then I still get a 0 for my Ratio. Should I change them from INT to FLOAT or is the CAST really taking care of that? – MCP_infiltrator Jun 11 '13 at 18:35
@MCP_infiltrator you actually need to cast either the num or denom to float, rather than the ratio as a whole. See my answer. – Chad Jun 11 '13 at 18:37

score 1 · Answer 4 · answered Jun 11 '13 at 18:31

1

It's truncating due to integer division. You can perform regular division by casting.

INSERT INTO @TABLE1
SELECT
A.NUM, A.DENOM, CAST(A.NUM AS FLOAT)/A.DENOM

answered Jun 11 '13 at 18:31

Chad

7,279
2
24
34

This for me, did not work. I ended up doing `CAST(A.NUM/A.DENOM) AS FLOAT` and changing `RATIO` in `@TABLE1` to a `FLOAT` as well and that did the trick. – MCP_infiltrator Jun 11 '13 at 18:45
1

I missed the fact that your table column was an `int`. This would have worked as well if the destination column was a `float`. – Chad Jun 11 '13 at 18:48
It's alright, thanks for the help though, if I did not fix the part you mentioned I still would have not gotten a right answer. – MCP_infiltrator Jun 11 '13 at 18:49

Getting two counts and then dividing them

4 Answers4

Linked