SELECT SUM - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

SELECT SUM

Author

Topic

divan
Posting Yak Master

153 Posts

Posted - 2012-10-19 : 11:58:13

I have a very long select statement in my script and one of the fields that I am trying to create is a written premium field

so when I use the following statement
SUM (P1.WRITTEN_PREMIUM) AS WRITTEN_PREMIUM

where I have defined P1 to be
INNER JOIN PREMIUM_DETAIL P1 ON M.POLICY_NUMBER = P1.POLICY_NUMBER AND M.SEQUENCE_NUMBER = P1.SEQUENCE_NUMBER AND P1.POLICY_DATE_TIME = (SELECT MAX (POLICY_DATE_TIME) FROM PREMIUM_DETAIL P2 WHERE P1.POLICY_NUMBER = P2.POLICY_NUMBER) ---10/19/12

I get the following error message when trying to compile the script

Column 'MPL_EXPOSURE.POLICY_NUMBER' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

but when I have (P1.WRITTEN_PREMIUM) AS WRITTEN_PREMIUM it compiles fine and runs

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-10-19 : 12:20:07

In general, the rule is that any column that you have in your select list must be either inside of an aggregate function (SUM, MAX, MIN etc.) or, if they are not, such columns must be in the group by clause as well.

If you can post your entire query including the group by clause and the select list, that might make it easier for someone looking at it to diagnose the problem.

divan
Posting Yak Master

153 Posts

Posted - 2012-10-19 : 12:26:51

Hi Sunitabeck.. I am glad you are looking at this as I know that you will have a solution for it..here is what I have

SELECT DISTINCT
M.POLICY_NUMBER,
P.POL_EFF_DATE,
P.POL_EXP_DATE,
E4.DESCRIPTION AS STATUS_1,
E5.DESCRIPTION AS POL_EXP_TYPE,
E3.DESCRIPTION AS TYPE_LMT,
M.CLIENT_NUMBER,
LTRIM(ISNULL(C.FNAME1,'') + ' '+ISNULL(C.INIT1,'') + ' ' + ISNULL(C.LNAME1,'') + ',' + ISNULL(C.SUFFIX1,'')) AS INSURED,
N.CONTACT_ID AS TRIBUTE_CONTACT_ID,
E6.DESCRIPTION AS EXPOSURE_TYPE,
M.POLICY_TYPE AS GRP_IND_TYPE,

---- ANCILLARY FIELDS
CASE WHEN M.TYPE_ANCILLARY = 'XXX' THEN ' ' ELSE M.TYPE_ANCILLARY END AS TYPE_ANCILLARY,
CASE WHEN TYPE_ANCILLARY = 'XXX' THEN ' ' ELSE E2.DESCRIPTION END AS PHNX_ANCILLARY_SPECIALTY_DESC,
CASE WHEN T1.NOVA_CODE IS NULL THEN ' ' ELSE T1.NOVA_CODE END AS NOVA_ANCILLARY_CODE,
CASE WHEN T1.NOVA_DESCRIPTION IS NULL THEN ' ' ELSE T1.NOVA_DESCRIPTION END AS NOVA_ANCILLARY_DESCRIPTION,

----PHYSICIAN FIELDS
CASE WHEN E.CODE = 'XXX' THEN '' ELSE E.CODE END AS PHNX_SPECIALTY_CODE,
CASE WHEN SPECIALTY = 'XXX' THEN ' ' ELSE E.DESCRIPTION END AS PHNX_PHYSICIAN_SPECIALTY_DESC,
CASE WHEN T.NOVA_CODE IS NULL THEN ' ' ELSE T.NOVA_CODE END AS PHYSICIAN_NOVA_CODE,
CASE WHEN T.NOVA_DESCRIPTION IS NULL THEN ' ' ELSE T.NOVA_DESCRIPTION END AS PHYSICIAN_NOVA_DESCRIPTION,

COALESCE(BASE_PREM,0) AS BASE_PREMIUM,
COALESCE(PHYS_TMB_PREM,0) AS TMB_PREMIUM,
COALESCE(BASE_PREM,0)+COALESCE(PHYS_TMB_PREM,0) AS TOTAL_BASE_PREM,
COALESCE(ENT_PREM_PCT,0) AS ENTITY_PREMIUM_PERCENTAGE,

GROUP_DISC,
CONVERT (DECIMAL (10,0),ROUND((COALESCE(BASE_PREM,0)+COALESCE(PHYS_TMB_PREM,0))
*(COALESCE(GROUP_DISC,0)/100.0),0)) AS GROUP_DISC_AMOUNT,

DISCOUNT_2 AS MARKET_DISC_PERC,
CONVERT (DECIMAL (10,0),ROUND((COALESCE(BASE_PREM,0)+COALESCE(PHYS_TMB_PREM,0))
*(1.0+COALESCE(GROUP_DISC,0)/100.0)
*(COALESCE(DISCOUNT_2,0)/100.0),0)) AS MARKET_DISCOUNT_AMOUNT,

EXPER_DISC AS EXPER_DISC_PERC,
CONVERT (DECIMAL (10,0),ROUND((COALESCE(BASE_PREM,0)+COALESCE(PHYS_TMB_PREM,0))
*(1.0+COALESCE(GROUP_DISC,0)/100.0)
*(1.0+COALESCE(DISCOUNT_2,0)/100.0)
*(COALESCE(EXPER_DISC,0)/100.0),0)) AS EXPER_DISC_AMOUNT,

RISK_MGMT AS RISK_MGMT_PERC,
CONVERT (DECIMAL (10,0),ROUND((COALESCE(BASE_PREM,0)+COALESCE(PHYS_TMB_PREM,0))
*(1.0+COALESCE(GROUP_DISC,0)/100.0)
*(1.0+COALESCE(DISCOUNT_2,0)/100.0)
*(1.0+COALESCE(EXPER_DISC,0)/100.0)
*(COALESCE(RISK_MGMT,0)/100.0),0)) AS RISK_MGMT_AMOUNT,

DISCOUNT_1 AS IPA_MEMBER_PERCENT_DISC,
CONVERT (DECIMAL (10,0),ROUND((COALESCE(BASE_PREM,0)+COALESCE(PHYS_TMB_PREM,0))
*(1.0+COALESCE(GROUP_DISC,0)/100.0)
*(1.0+COALESCE(DISCOUNT_2,0)/100.0)
*(1.0+COALESCE(EXPER_DISC,0)/100.0)
*(1.0+COALESCE(RISK_MGMT,0)/100.0)
*(COALESCE(DISCOUNT_1,0)/100.0),0)) AS IPA_MEMBER_PERCENT_DISC_AMOUNT,

DISCOUNT_3 AS ASSOC_MEMBER_DISC_PERCENT,
CONVERT (DECIMAL (10,0),ROUND((COALESCE(BASE_PREM,0)+COALESCE(PHYS_TMB_PREM,0))
*(1.0+COALESCE(GROUP_DISC,0)/100.0)
*(1.0+COALESCE(DISCOUNT_2,0)/100.0)
*(1.0+COALESCE(EXPER_DISC,0)/100.0)
*(1.0+COALESCE(RISK_MGMT,0)/100.0)
*(1.0+COALESCE(DISCOUNT_1,0)/100.0)
*(COALESCE(DISCOUNT_3,0)/100.0),0)) AS ASSOC_MEMBER_DISC_DISC_AMOUNT,

MANUAL_BLENDING AS MANUAL_BLENDING_PERCENT_DISC,
CONVERT (DECIMAL (10,0),ROUND((COALESCE(BASE_PREM,0)+COALESCE(PHYS_TMB_PREM,0))
*(1.0+COALESCE(GROUP_DISC,0)/100.0)
*(1.0+COALESCE(DISCOUNT_2,0)/100.0)
*(1.0+COALESCE(EXPER_DISC,0)/100.0)
*(1.0+COALESCE(RISK_MGMT,0)/100.0)
*(1.0+COALESCE(DISCOUNT_1,0)/100.0)
*(1.0+COALESCE(DISCOUNT_3,0)/100.0)
*(COALESCE(MANUAL_BLENDING,0)/100.0),0)) AS MANUAL_BLENDING_DISC_AMOUNT,

PART_TIME_DISC AS PART_TIME_DISC_PERCENT,
CONVERT (DECIMAL (10,0),ROUND((COALESCE(BASE_PREM,0)+COALESCE(PHYS_TMB_PREM,0))
*(1.0+COALESCE(GROUP_DISC,0)/100.0)
*(1.0+COALESCE(DISCOUNT_2,0)/100.0)
*(1.0+COALESCE(EXPER_DISC,0)/100.0)
*(1.0+COALESCE(RISK_MGMT,0)/100.0)
*(1.0+COALESCE(DISCOUNT_1,0)/100.0)
*(1.0+COALESCE(DISCOUNT_3,0)/100.0)
*(1.0+COALESCE(MANUAL_BLENDING,0)/100.0)
*(COALESCE(PART_TIME_DISC,0)/100.0),0)) AS PART_TIME_DISC_AMOUNT,

NEW_PRAC_DISC AS NEW_PRAC_DISC_PERCENT,
CONVERT (DECIMAL (10,0),ROUND((COALESCE(BASE_PREM,0)+COALESCE(PHYS_TMB_PREM,0))
*(1.0+COALESCE(GROUP_DISC,0)/100.0)
*(1.0+COALESCE(DISCOUNT_2,0)/100.0)
*(1.0+COALESCE(EXPER_DISC,0)/100.0)
*(1.0+COALESCE(RISK_MGMT,0)/100.0)
*(1.0+COALESCE(DISCOUNT_1,0)/100.0)
*(1.0+COALESCE(DISCOUNT_3,0)/100.0)
*(1.0+COALESCE(MANUAL_BLENDING,0)/100.0)
*(1.0+COALESCE(PART_TIME_DISC,0)/100.0)
*(COALESCE(NEW_PRAC_DISC,0)/100.0),0)) AS NEW_PRAC_DISC_AMOUNT,

----ADDITIONAL MISSISSIPPI DISCOUNTS

'0' AS CLAIMS_MADE_DEBIT_PERCENT,
'0' AS CLAIMS_MADE_DEBIT_AMOUNT,

'0' AS TAIL_NOT_BOUGHT_PERCENT,
'0' AS TAIL_NOT_BOUGHT_AMOUNT,

'0' AS GRIEVANCE_PERCENT,
'0' AS GRIEVANCE_AMOUNT,

'0' AS FELONY_MISDEMEANOR_PERCENT,
'0' AS FELONY_MISDEMEANOR_AMOUNT,

'0' AS SICK_PERCENT,
'0' AS SICK_AMOUNT,

'0' AS REVIEW_PERCENT,
'0' AS REVIEW_AMOUNT,

'0' AS NEW_PRACTICE_PERCENT,
'0' AS NEW_PRACTICE_AMOUNT,

'0' AS PART_TIME_PERCENT,
'0' AS PART_TIME_AMOUNT,

'0' AS ABUSE_PERCENT,
'0' AS ABUSE_AMOUNT,

'0' AS LICENSE_PERCENT,
'0' AS LICENSE_AMOUNT,

'0' AS MISCONDUCT_PERCENT,
'0' AS MISCONDUCT_AMOUNT,

'0' AS RELAPSE_PERCENT,
'0' AS RELAPSE_AMOUNT,

'0' AS NO_COVERAGE_PERCENT,
'0' AS NO_COVERAGE_AMOUNT,

'0' AS FDA_APPROVED_PERCENT,
'0' AS FDA_APPROVED_AMOUNT,

'0' AS EXPIRING_COVERAGE_PERCENT,
'0' AS EXPIRING_COVERAGE_AMOUNT,

'0' AS IMP_PHYS_PROG_PERCENT,
'0' AS IMP_PHYS_PROG_AMOUNT,

'0' AS IMP_NOT_RENEWED_PERCENT,
'0' AS NOT_RENEWED_AMOUNT,

'0' AS OTHER_DEBIT_PERCENT,
'0' AS OTHER_DEBIT_AMOUNT,

'0' AS OTHER_CREDIT_PERCENT,
'0' AS OTHER_CREDIT_AMOUNT,

'0' AS LOSS_FREQUENCY_PERCENT,
'0' AS LOSS_FREQUENCY_AMOUNT,

'0' AS LONGEVITY_CREDIT_PERCENT,
'0' AS LONGEVITY_CREDIT_AMOUNT,

'0' AS VIC_LIAB_DEBIT_PERCENT,
'0' AS VIC_LIAB_DEBIT_AMOUNT,

'0' AS MISC_DEBIT_1_PERCENT,
'0' AS MISC_DEBIT_1_AMOUNT,

'0' AS MISC_DEBIT_2_PERCENT,
'0' AS MISC_DEBIT_2_AMOUNT,

REG_PROC_PREM AS REG_PROC_PREM,
CONVERT (DECIMAL (10,0),(COALESCE(BASE_PREM,0)+COALESCE(PHYS_TMB_PREM,0))
*(1.0+COALESCE(GROUP_DISC,0)/100.0)
*(1.0+COALESCE(DISCOUNT_2,0)/100.0)
*(1.0+COALESCE(EXPER_DISC,0)/100.0)
*(1.0+COALESCE(RISK_MGMT,0)/100.0)
*(1.0+COALESCE(DISCOUNT_1,0)/100.0)
*(1.0+COALESCE(DISCOUNT_3,0)/100.0)
*(1.0+COALESCE(MANUAL_BLENDING,0)/100.0)
*(1.0+COALESCE(PART_TIME_DISC,0)/100.0)
*(1.0+COALESCE(NEW_PRAC_DISC,0)/100.0)
+ COALESCE(REG_PROC_PREM,0)) AS EXPR_PREM,

SUM (P1.WRITTEN_PREMIUM) AS WRITTEN_PREMIUM ---10/19/12

-- FROM PREMIUM_DETAIL
-- WHERE POLICY_NUMBER = 'CP 10121' AND POLICY_DATE_TIME = '2011-12-16 13:17:53.000'
-- AND TERMINATION_DATE IS NULL AND SEQUENCE_NUMBER = 3 AND TBNAME='PB_VARNAME'

---P.WRITTEN_PREMIUM AS WRITTEN_PREMIUM ---10/18/12

INTO #TEMP1

FROM MPL_EXPOSURE M
INNER JOIN POLICY P ON M.POLICY_NUMBER = P.POLICY_NUMBER AND M.POLICY_DATE_TIME = P.POLICY_DATE_TIME
INNER JOIN REGISTER R ON M.POLICY_NUMBER = R.POLICY_NUMBER AND M.POLICY_DATE_TIME = R.POLICY_DATE_TIME
INNER JOIN EDIT_LONG_CODE E ON E.TBNAME = 'MPL_EXPOSURE' AND E.NAME = 'SPECIALTY' AND E.CODE = SPECIALTY
INNER JOIN EDIT_LONG_CODE E2 ON E2.TBNAME = 'MPL_EXPOSURE' AND E2.NAME = 'TYPE_ANCILLARY' AND E2.CODE = TYPE_ANCILLARY
INNER JOIN EDIT_SHORT_CODE E3 ON E3.TBNAME = 'MPL_EXPOSURE' AND E3.NAME = 'TYPE_LMT' AND E3.CODE = TYPE_LMT
INNER JOIN EDIT_SHORT_CODE E4 ON E4.TBNAME = 'REGISTER' AND E4.NAME = 'STATUS_1' AND E4.CODE = R.STATUS_1
INNER JOIN EDIT_SHORT_CODE E5 ON E5.TBNAME = 'MPL_EXPOSURE' AND E5.NAME = 'POL_EXP_TYPE' AND E5.CODE = M.POL_EXP_TYPE
INNER JOIN EDIT_SHORT_CODE E6 ON E6.TBNAME = 'MPL_EXPOSURE' AND E6.NAME = 'EXPOSURE_TYPE' AND E6.CODE = M.EXPOSURE_TYPE
LEFT OUTER JOIN TRIBUTE_SPECIALTY_MAPPING T ON E.CODE = T.OPCO_CODE AND T.NAME = 'SPECIALTY'
LEFT OUTER JOIN TRIBUTE_SPECIALTY_MAPPING T1 ON E2.CODE = T1.OPCO_CODE AND T1.NAME = 'TYPE_ANCILLARY'
INNER JOIN CLIENT C ON M.CLIENT_NUMBER = C.CLIENT_NUMBER
LEFT OUTER JOIN PHX_NOVA_CONTACT_INFO_TABLE N ON C.CLIENT_NUMBER = N.EDI_SOURCE_PRIMARY_KEY_VALUE ----072712
INNER JOIN CLIENT C2 ON M.CLIENT_NUMBER = C2.CLIENT_NUMBER
INNER JOIN PREMIUM_DETAIL P1 ON M.POLICY_NUMBER = P1.POLICY_NUMBER AND M.SEQUENCE_NUMBER = P1.SEQUENCE_NUMBER AND P1.POLICY_DATE_TIME = (SELECT MAX (POLICY_DATE_TIME) FROM PREMIUM_DETAIL P2 WHERE P1.POLICY_NUMBER = P2.POLICY_NUMBER) ---10/19/12

-- WHERE ((M.EXPOSURE_TYPE = '2'AND M.TYPE_LMT IN ('4','9') AND T1.NAME = 'TYPE_ANCILLARY' AND T1.OPCO_CODE = E2.CODE ) OR (M.EXPOSURE_TYPE = '1' AND T.NAME = 'SPECIALTY' AND T.OPCO_CODE = E.CODE))
WHERE ((M.EXPOSURE_TYPE = '2'AND M.TYPE_LMT IN ('4','9') AND T1.NAME = 'TYPE_ANCILLARY' AND T1.OPCO_CODE = E2.CODE ) OR (M.EXPOSURE_TYPE = '1' AND M.TYPE_LMT <> '8' AND T.NAME = 'SPECIALTY' AND T.OPCO_CODE = E.CODE)) ---10/16/12
AND P.POL_EFF_DATE BETWEEN '1/1/12' AND '6/30/12'
AND NOT (M.POLICY_NUMBER LIKE 'MS%' OR M.POLICY_NUMBER LIKE 'Q%')
AND M.POLICY_DATE_TIME = (SELECT MAX(POLICY_DATE_TIME)FROM MPL_EXPOSURE M3 WHERE M.POLICY_NUMBER = M3.POLICY_NUMBER)
AND R.STATUS_1<>'6'
AND M.CANCEL_TYPE NOT IN ('1','2')
AND M.EXP_PREM <> 0
AND M.POLICY_TYPE IN ('2','3')----10/16/12

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-10-19 : 12:37:36

You have two choices:

1. Change the SUM as shown below:

SUM(P1.WRITTEN_PREMIUM) OVER() AS WRITTEN_PREMIUM

2. Put every other column except constants into a group by clause at the very end. Remove the aliases though. So your group by clause would be like:

GROUP BY
       M.POLICY_NUMBER,
       P.POL_EFF_DATE,
       P.POL_EXP_DATE,
       E4.DESCRIPTION, -- no alias       AS STATUS_1,
       E5.DESCRIPTION,  -- no alias       AS POL_EXP_TYPE,
       ....

If neither of those give you what you want, can you post some sample data?

divan
Posting Yak Master

153 Posts

Posted - 2012-10-19 : 13:01:24

SUM(P1.WRITTEN_PREMIUM) OVER() AS WRITTEN_PREMIUM this statement compiles fine but for some reason it is ignoring
the inner join for P1 and it is summing all the written premiums for all the records.

INNER JOIN PREMIUM_DETAIL P1 ON M.POLICY_NUMBER = P1.POLICY_NUMBER AND M.SEQUENCE_NUMBER = P1.SEQUENCE_NUMBER AND P1.POLICY_DATE_TIME = (SELECT MAX (POLICY_DATE_TIME) FROM PREMIUM_DETAIL P2 WHERE

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-10-19 : 13:12:47

Change the OVER clause to insert any columns you want the data to be grouped by.
For example,


SUM(P1.WRITTEN_PREMIUM) OVER(PARTITION BY M.POLICY_NUMBER ) AS WRITTEN_PREMIUM

divan
Posting Yak Master

153 Posts

Posted - 2012-10-19 : 13:28:50

Thanks Sunitabeck... It worked great.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources