Display row value as a TABLE column using SQL select statement

,

Requirement:

Summarize PIFAmt column divide by Summarize Factors column by StateName column and diplay prior & current year data as a table column by auto and property business values.

Table Structure:

Table Data:

You could avoid the following errors when you execute the enclosed SQL select statement and display the result as I explained in my requirement.

Errors:

Server: Msg 8134, Level 16, State 1, Line 1

Divide by zero error encountered.

Server: Msg 130, Level 15, State 1, Line 3

Cannot perform an aggregate function on an expression containing an aggregate or a subquery.

Results:

SELECT STATENAME,
SUM(CASE WHEN EFFYEAR = 2006 AND BUSINESS = 'AUTO' THEN PIFAMT END) / 
SUM(CASE WHEN FACTORS > 0 AND EFFYEAR = 2006 AND BUSINESS = 'AUTO' THEN FACTORS END) AS AUTO_PIF_PREV_YR,
SUM(CASE WHEN EFFYEAR = 2007 AND BUSINESS = 'AUTO' THEN PIFAMT END) / 
SUM(CASE WHEN FACTORS > 0 AND EFFYEAR = 2007 AND BUSINESS = 'AUTO' THEN FACTORS END) AS AUTO_PIF_CURR_YR,
SUM(CASE WHEN EFFYEAR = 2006 AND BUSINESS = 'PROPERTY' AND SEGMENT = 'HOMEOWNERS' THEN PIFAMT END) / 
SUM(CASE WHEN FACTORS > 0 AND EFFYEAR = 2006 AND BUSINESS = 'PROPERTY' AND SEGMENT = 'HOMEOWNERS' THEN FACTORS END) AS PROP_PIF_PREV_YR,
SUM(CASE WHEN EFFYEAR = 2007 AND BUSINESS = 'PROPERTY' AND SEGMENT = 'HOMEOWNERS' THEN PIFAMT END) / 
SUM(CASE WHEN FACTORS > 0 AND EFFYEAR = 2007 AND BUSINESS = 'PROPERTY' AND SEGMENT = 'HOMEOWNERS' THEN FACTORS END) AS PROP_PIF_CURR_YR
FROM FACT
WHERE BUSINESS IN ('AUTO', 'PROPERTY') AND EFFYEAR >= 2006 AND 
SEGMENT = (CASE WHEN BUSINESS = 'AUTO' THEN SEGMENT ELSE 'HOMEOWNERS' END)
GROUP BY STATENAME

GO

Rate

1.27 (11)

Share

Share

Rate

1.27 (11)