locked
Help With Calculation RRS feed

  • Question

  • For my divide column, the result set that is returned is 1, 1.  However, if you look at the data it is as follows, so why does the output return 1, 1 and how do I fix that?

         D  -----  IT

    SC 0           0

    TN 3           2

    Create Table Data
    (
    	ID int
    	,[state] varchar(10)
    	,shipstatus varchar(25)
    )
    
    Insert Into Data Values 
    (1,'TN', 'Delivered'),
    (2, 'TN', 'Delivered'), 
    (3, 'TN', 'Delivered'), 
    (4, 'TN', 'In Transit'),
    (5, 'TN', 'In Transit'), 
    (6, 'TN', 'Pending'), 
    (7, 'SC', 'Pending'), 
    (8, 'SC', 'Pending'), 
    (9, 'SC', 'Pending')
    
    Select
    [state]
    ,count(case when shipstatus in ('Pending') then id else 0 end) As Pending
    ,count(case when shipstatus in ('Delivered') then id else 0 end) As Delivered
    ,count(case when shipstatus in ('In Transit') then id else 0 end) As Transit
    ,count(case when shipstatus in ('Delivered') then id else 0 end)/count(case when shipstatus in ('In Transit') then id else 0 end) As [Divide]
    FROM Data
    GROUP BY [state]
    Order by [state] ASC
    
    Drop Table Data

    Thursday, August 6, 2015 3:29 PM

Answers

  • It's because you're using COUNT aggregate and it counts 0. You should have either used SUM (1 and 0) or you can change it this way:

    declare @Data table
    (
    	ID int
    	,[state] varchar(10)
    	,shipstatus varchar(25)
    )
    
    Insert Into @Data Values 
    (1,'TN', 'Delivered'),
    (2, 'TN', 'Delivered'), 
    (3, 'TN', 'Delivered'), 
    (4, 'TN', 'In Transit'),
    (5, 'TN', 'In Transit'), 
    (6, 'TN', 'Pending'), 
    (7, 'SC', 'Pending'), 
    (8, 'SC', 'Pending'), 
    (9, 'SC', 'Pending')
    
    Select
    [state]
    ,count(case when shipstatus in ('Pending') then id  end) As Pending
    ,count(case when shipstatus in ('Delivered') then id end) As Delivered
    ,count(case when shipstatus in ('In Transit') then id  end) As Transit
    ,count(case when shipstatus in ('Delivered') then id  end)*1.0/NULLIF(count(case when shipstatus in ('In Transit') then id end),0) As [Divide]
    FROM @Data
    GROUP BY [state]
    Order by [state] ASC
    


    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog


    My TechNet articles

    Thursday, August 6, 2015 3:41 PM