Case statement and divide by zero error

Question

I'm getting a divide by zero error and Im not sure how to handle it. Using a sql server database.

Script:

SELECT  Race ,
        [2012] = MAX(CASE WHEN a.[Year] = 2012 THEN [Count]
                     END) ,
        [% Inc Dec] = ( MAX(CAST(CASE WHEN a.[Year] IS NULL THEN ''
                        ELSE a.[Year] = 2011 THEN [Count]
                                 END AS FLOAT))
                        - MAX(CAST(CASE WHEN a.[Year] IS NULL THEN ''
                        ELSE a.[Year] = 2012 THEN [Count]
                                   END AS FLOAT)) )
        / MAX(CAST(CASE WHEN a.[Year] IS NULL THEN ''
                        ELSE a.[Year] = 2012 THEN [Count]
                   END AS FLOAT)) ,
        [2011] = MAX(CASE WHEN a.[Year] = 2011 THEN [Count]
                     END) ,
        [% Inc Dec] = ( MAX(CAST(CASE WHEN a.[Year] = 2010 THEN [Count]
                                 END AS FLOAT))
                        - MAX(CAST(CASE WHEN a.[Year] = 2011 THEN [Count]
                                   END AS FLOAT)) )
        / MAX(CAST(CASE WHEN a.[Year] = 2010 THEN [Count]
                   END AS FLOAT)) ,
        [2010] = MAX(CASE WHEN a.[Year] = 2010 THEN [Count]
                     END) ,
        [2009] = MAX(CASE WHEN a.[Year] = 2009 THEN [Count]
                     END) ,
        [% Inc Dec] = ( MAX(CAST(CASE WHEN a.[Year] = 2008 THEN [Count]
                                 END AS FLOAT))
                        - MAX(CAST(CASE WHEN a.[Year] = 2009 THEN [Count]
                                   END AS FLOAT)) )
        / MAX(CAST(CASE WHEN a.[Year] = 2009 THEN [Count]
                   END AS FLOAT)) 
FROM    @Data

I attempted to change the first two case statements but it was incorrect. Any help or push in the right direction would be appreciated.

Answer 1

I'm assuming a is an alias for @Data, or it's in the same table/source as race and [count], so your data looks like this:

race    YEAR      COUNT
w       2012      10
w       2011      50
w       2010      100
b       2010      200
b       2009      75

and you expect the data to decrease over time. I'd use a self-join to insure both the high year and low year have values before you subtract and divide, like this:

SELECT
 race, 
 y1.[YEAR] AS yr1, y1.[COUNT] AS cnt1, 
 y2.[YEAR] AS yr2, y2.[COUNT] AS cnt2, 
 (cnt2 - cnt1) / cnt1 AS pct_inc_dec
FROM 
 @DATA y1 INNER JOIN 
 @DATA y2 ON 
 y1.race = y2.race AND
 y1.YEAR = y2.YEAR - 1  -- y1 is the later year

then you don't have to worry about division by zero at all, unless the count itself is zero.

---

with the code you provided, then, the self-join would be applied on the stored table after the cross apply's pivot. you can combine your older year's criteria so it doesn't have to go in its own table:

DECLARE @DATA TABLE
    (
      [YEAR] INT ,
      [COUNT] INT ,
      race VARCHAR(50)  
    )

INSERT  INTO @DATA
SELECT  
    Race ,[COUNT], [YEAR]
FROM    
            (SELECT  
                dt.YEAR AS YEAR ,
                SUM(ISNULL(caucasian_enrollment, 0)) AS caucasian ,
                SUM(ISNULL(black_or_african_american_enrollment, 0)) AS Black ,
                SUM(ISNULL(asian_enrollment,0)) AS Asian ,
                SUM(ISNULL(native_hawaiian_pacific_islander_enrollment, 0)) AS Native ,
                SUM(ISNULL(hispanic_enrollment, 0)) AS Hispanic ,
                SUM(ISNULL(american_indian_or_alaskan_ative_enrollment, 0)) AS [American Indian] ,
                SUM(ISNULL(multiracial_enrollment, 0)) AS multiracial ,
                SUM(ISNULL(unknown_ethnicity_enrollment, 0)) AS UNKNOWN ,
                SUM(ISNULL(male_enrollment, 0)) AS male,
                SUM(ISNULL(female_enrollment, 0)) AS female,
                SUM(ISNULL(Total_Enrollment, 0)) AS  [Total Enrollment]
            FROM    
                mart.dbo.f_s AS fes
                INNER JOIN TIME AS dt ON fes.time_key = dt.time_key
            WHERE   
                YEAR = '2012' 
            GROUP BY 
                dt.YEAR
        UNION ALL
            SELECT  
                dt.YEAR AS YEAR ,
                SUM(ISNULL(caucasian_enrollment, 0)) AS caucasian ,
                SUM(ISNULL(black_or_african_american_enrollment, 0)) AS Black ,
                SUM(ISNULL(asian_enrollment,0)) AS Asian ,
                SUM(ISNULL(native_hawaiian_pacific_islander_enrollment, 0)) AS Native,
                SUM(ISNULL(hispanic_enrollment, 0)) AS Hispanic,
                SUM(ISNULL(american_indian_or_alaskan_ative_enrollment, 0)) AS [American Indian] ,
                SUM(ISNULL(multiracial_enrollment, 0)) AS multiracial,
                SUM(ISNULL(unknown_ethnicity_enrollment, 0)) AS UNKNOWN,
                SUM(ISNULL(male_enrollment, 0)) AS male ,
                SUM(ISNULL(female_enrollment, 0)) AS female,
                SUM(ISNULL(Total_Enrollment, 0)) AS  [Total Enrollment]
            FROM    
                F5A_education_mart_IAK12.dbo.fact_enrollment_school AS fes
                INNER JOIN dim_time AS dt ON fes.time_key = dt.time_key
            WHERE   
                YEAR <= '2011' AND YEAR >= '2008'
            GROUP BY 
                dt.YEAR 
    ) d  CROSS APPLY 
    ( VALUES 
        ( 'Caucasian', caucasian, [YEAR])
        , ( 'Black', Black, [YEAR])
        , ( 'Asian', Asian, [YEAR]) 
        , ('Native', Native, [YEAR]) 
        , ('Hispanic', Hispanic, [YEAR]) 
        , ('American Indian', [American Indian], [YEAR]) 
        , ('Multiracial', multiracial, [YEAR])
        , ('Unkown', UNKNOWN, [YEAR]) 
        , ('Male', male, [YEAR]) 
        , ('Female', female, [YEAR]) 
        , ('Total Enrollment', [Total Enrollment], [YEAR]) 
        ) a ( Race, [COUNT], [YEAR] ) 
 GROUP BY 
    Race    

SELECT
    race, 
    y1.[YEAR] AS yr1, y1.[COUNT] AS cnt1, 
    y2.[YEAR] AS yr2, y2.[COUNT] AS cnt2, 
    (cnt2 - cnt1) / cnt1 AS pct_inc_dec
FROM 
    @DATA y1 INNER JOIN 
    @DATA y2 ON 
    y1.race = y2.race AND
    y1.YEAR = y2.YEAR - 1  -- y1 is the later year

Answer 2

Thanks for the quick response T I and Martin. Originally I didnt post the full script because I was only having an issue with my case statement and I couldnt get ISNULL to work. Below is the full result and answer:

    DECLARE @Data TABLE
    (
      [Year] INT ,
      caucasian INT ,
      Black INT ,
      Asian INT ,
      Native INT ,
      Hispanic INT ,
      [American Indian] INT ,
      Multiracial INT ,
      UNKNOWN INT ,
      Male INT ,
      Female INT , 
      [Total Enrollment] INT 
    )

INSERT  INTO @Data
        SELECT  dt.year AS Year ,
                SUM(ISNULL(caucasian_enrollment, 0)) ,
                SUM(ISNULL(black_or_african_american_enrollment, 0)) ,
                SUM(ISNULL(asian_enrollment,0)) ,
                SUM(ISNULL(native_hawaiian_pacific_islander_enrollment, 0)) ,
                SUM(ISNULL(hispanic_enrollment, 0)) ,
                SUM(ISNULL(american_indian_or_alaskan_ative_enrollment, 0)) ,
                SUM(ISNULL(multiracial_enrollment, 0)) ,
                SUM(ISNULL(unknown_ethnicity_enrollment, 0)) ,
                SUM(ISNULL(male_enrollment, 0)) ,
                SUM(ISNULL(female_enrollment, 0)) ,
                SUM(ISNULL(Total_Enrollment, 0)) 
        FROM    mart.dbo.f_s AS fes
                INNER JOIN time AS dt ON fes.time_key = dt.time_key
                LEFT OUTER JOIN building AS db ON fes.building_key = db.building_key
        WHERE   year = '2012'
        GROUP BY dt.year
        UNION ALL
        SELECT  dt.year AS Year ,
                SUM(ISNULL(caucasian_enrollment, 0)) ,
                SUM(ISNULL(black_or_african_american_enrollment, 0)) ,
                SUM(ISNULL(asian_enrollment,0)) ,
                SUM(ISNULL(native_hawaiian_pacific_islander_enrollment, 0)) ,
                SUM(ISNULL(hispanic_enrollment, 0)) ,
                SUM(ISNULL(american_indian_or_alaskan_ative_enrollment, 0)) ,
                SUM(ISNULL(multiracial_enrollment, 0)) ,
                SUM(ISNULL(unknown_ethnicity_enrollment, 0)) ,
                SUM(ISNULL(male_enrollment, 0)) ,
                SUM(ISNULL(female_enrollment, 0)) ,
                SUM(ISNULL(Total_Enrollment, 0)) 
        FROM    F5A_education_mart_IAK12.dbo.fact_enrollment_school AS fes
                INNER JOIN dim_time AS dt ON fes.time_key = dt.time_key
                LEFT OUTER JOIN dim_building AS db ON fes.building_key = db.building_key
        WHERE   year = '2011'
        GROUP BY dt.year
        UNION ALL
        SELECT  dt.year AS Year ,
                SUM(ISNULL(caucasian_enrollment, 0)) ,
                SUM(ISNULL(black_or_african_american_enrollment, 0)) ,
                SUM(ISNULL(asian_enrollment,0)) ,
                SUM(ISNULL(native_hawaiian_pacific_islander_enrollment, 0)) ,
                SUM(ISNULL(hispanic_enrollment, 0)) ,
                SUM(ISNULL(american_indian_or_alaskan_ative_enrollment, 0)) ,
                SUM(ISNULL(multiracial_enrollment, 0)) ,
                SUM(ISNULL(unknown_ethnicity_enrollment, 0)) ,
                SUM(ISNULL(male_enrollment, 0)) ,
                SUM(ISNULL(female_enrollment, 0)) ,
                SUM(ISNULL(Total_Enrollment, 0)) 
        FROM    F5A_education_mart_IAK12.dbo.fact_enrollment_school AS fes
                INNER JOIN dim_time AS dt ON fes.time_key = dt.time_key
                LEFT OUTER JOIN dim_building AS db ON fes.building_key = db.building_key
        WHERE   year = '2010'
        GROUP BY dt.year
        UNION ALL
        SELECT  dt.year AS Year ,
                SUM(ISNULL(caucasian_enrollment, 0)) ,
                SUM(ISNULL(black_or_african_american_enrollment, 0)) ,
                SUM(ISNULL(asian_enrollment,0)) ,
                SUM(ISNULL(native_hawaiian_pacific_islander_enrollment, 0)) ,
                SUM(ISNULL(hispanic_enrollment, 0)) ,
                SUM(ISNULL(american_indian_or_alaskan_ative_enrollment, 0)) ,
                SUM(ISNULL(multiracial_enrollment, 0)) ,
                SUM(ISNULL(unknown_ethnicity_enrollment, 0)) ,
                SUM(ISNULL(male_enrollment, 0)) ,
                SUM(ISNULL(female_enrollment, 0)) ,
                SUM(ISNULL(Total_Enrollment, 0)) 
        FROM    F5A_education_mart_IAK12.dbo.fact_enrollment_school AS fes
                INNER JOIN dim_time AS dt ON fes.time_key = dt.time_key
                LEFT OUTER JOIN dim_building AS db ON fes.building_key = db.building_key
        WHERE   year = '2009'
        GROUP BY dt.year
        UNION ALL
        SELECT  dt.year AS Year ,
                SUM(ISNULL(caucasian_enrollment, 0)) ,
                SUM(ISNULL(black_or_african_american_enrollment, 0)) ,
                SUM(ISNULL(asian_enrollment,0)) ,
                SUM(ISNULL(native_hawaiian_pacific_islander_enrollment, 0)) ,
                SUM(ISNULL(hispanic_enrollment, 0)) ,
                SUM(ISNULL(american_indian_or_alaskan_ative_enrollment, 0)) ,
                SUM(ISNULL(multiracial_enrollment, 0)) ,
                SUM(ISNULL(unknown_ethnicity_enrollment, 0)) ,
                SUM(ISNULL(male_enrollment, 0)) ,
                SUM(ISNULL(female_enrollment, 0)) ,
                SUM(ISNULL(Total_Enrollment, 0)) 
        FROM    F5A_education_mart_IAK12.dbo.fact_enrollment_school AS fes
                INNER JOIN dim_time AS dt ON fes.time_key = dt.time_key
                LEFT OUTER JOIN dim_building AS db ON fes.building_key = db.building_key
        WHERE   year = '2008'
        GROUP BY dt.year 
--Race 2012 % inc/dec 2011 % inc/dec 2010
--Caucasian 3000 -.33 4000 .6 2500
--Black 1000 -.5 2000 .25 1500
--Asian 100 .5 50 .4 30

SELECT  Race ,
        [2012] = MAX(CASE WHEN a.[Year] = 2012 THEN [Count]
                     END) ,
        [% Inc Dec] = ( MAX(CAST(CASE WHEN a.[Year] = 2011 THEN [Count]
                                 END AS FLOAT))
                        - MAX(CAST(CASE WHEN a.[Year] = 2012 THEN [Count]
                    END AS FLOAT)) )
        / NULLIF(MAX(CAST(CASE WHEN a.[Year] = 2012 THEN [Count]
                   END AS FLOAT)),0) ,
        [2011] = MAX(CASE WHEN a.[Year] = 2011 THEN [Count]
                     END) ,
        [% Inc Dec] = ( MAX(CAST(CASE WHEN a.[Year] = 2010 THEN [Count]
                                 END AS FLOAT))
                        - MAX(CAST(CASE WHEN a.[Year] = 2011 THEN [Count]
                                   END AS FLOAT)) )
        / NULLIF(MAX(CAST(CASE WHEN a.[Year] = 2010 THEN [Count]
                   END AS FLOAT)),0) ,
        [2010] = MAX(CASE WHEN a.[Year] = 2010 THEN [Count]
                     END) ,
        [% Inc Dec] = ( MAX(CAST(CASE WHEN a.[Year] = 2009 THEN [Count]
                                 END AS FLOAT))
                        - MAX(CAST(CASE WHEN a.[Year] = 2010 THEN [Count]
                    END AS FLOAT)) )
        / NULLIF(MAX(CAST(CASE WHEN a.[Year] = 2009 THEN [Count]
                   END AS FLOAT)),0) ,
        [2009] = MAX(CASE WHEN a.[Year] = 2009 THEN [Count]
                     END) ,
        [% Inc Dec] = ( MAX(CAST(CASE WHEN a.[Year] = 2008 THEN [Count]
                                 END AS FLOAT))
                        - MAX(CAST(CASE WHEN a.[Year] = 2009 THEN [Count]
                                   END AS FLOAT)) )
        / NULLIF(MAX(CAST(CASE WHEN a.[Year] = 2009 THEN [Count]
                   END AS FLOAT)), 0)
FROM    @Data
        CROSS APPLY ( VALUES 
        ( 'Caucasian', caucasian, [Year])
        , ( 'Black', Black, [Year])
        , ( 'Asian', Asian, [Year]) 
        , ('Native', Native, [Year]) 
        , ('Hispanic', Hispanic, [Year]) 
        , ('American Indian', [American Indian], [Year]) 
        , ('Multiracial', multiracial, [Year])
        , ('Unkown', unknown, [Year]) 
        , ('Male', male, [Year]) 
        , ('Female', female, [Year]) 
        , ('Total Enrollment', [Total Enrollment], [Year]) 
        ) a ( Race, [Count], [Year] ) GROUP BY Race