Avatar of Jim Horn
Jim HornFlag for United States of America asked on

GROUP BY expressions must refer to column names that appear in the select list

The below SELECT (actually INSERT without the INSERT block) returns a 'GROUP BY expressions must refer to column names that appear in the select list'.   Without the Sum() and GROUP BY, it works like a champ.

______

SELECT DISTINCT
      p.policy_no,
      CASE WHEN DATEDIFF(day, fep.cert_eff_dt_min, @start_date) > 365 THEN @acct_premium_renewal ELSE @acct_premium_first_year END as account,
      -- CAST(cap.current_annual_premium/@date_range_interval AS decimal(19, 2)),   Daily calculation, keeping here if needed in the future.
      Sum(CAST(DATEDIFF(day, pmg.paf_eff_dt - 1, @end_date) * cap.current_annual_premium / 365 as decimal(19,2))),
      pser.filing_state,
      l.life_state,
      @end_date as end_date,
      '- PRORATED' as status
FROM prod_dev_gecapital..ins_group ig
      INNER JOIN glfeed_policy_member_group pmg ON ig.grp_id = pmg.grp_id
      INNER JOIN prod_dev_gecapital..policy p ON pmg.series_id = p.series_id AND pmg.cert_no = p.cert_no
      INNER JOIN prod_dev_gecapital..v_life l ON l.life_id = p.life_id
      INNER JOIN prod_dev_gecapital..policy_series pser ON pser.series_id = p.series_id
      INNER JOIN prod_dev_gecapital..certificate c ON c.cert_no = p.cert_no AND c.series_id = p.series_id
      INNER JOIN glfeed_policy_with_cert_eff_dt_min fep ON c.cert_no = fep.cert_no AND c.series_id = fep.series_id
      INNER JOIN glfeed_premiums_annual cap ON c.cert_id = cap.cert_id
WHERE       pmg.premium_waiver_flg = 1 and
      (pmg.paf_created_dt BETWEEN @start_date AND @end_date) and
      ((
      c.cert_expiration_dt IS NULL
      AND ig.grp_id <> 496
      AND (c.cert_status_cd > 6 And c.cert_status_cd < 9)
       )
      OR
      (
      (c.cert_expiration_dt < @end_date And c.cert_expiration_dt <> c.cert_eff_dt)
      AND ig.grp_id <> 496  
      AND c.cert_status_cd = 9
      ))
GROUP BY p.policy_no,
      CASE WHEN DATEDIFF(day, fep.cert_eff_dt_min, @start_date) > 365 THEN @acct_premium_renewal ELSE @acct_premium_first_year END,
      -- CAST(cap.current_annual_premium/@date_range_interval AS decimal(19, 2)),   Daily calculation, keeping here if needed in the future.
      pser.filing_state,
      l.life_state,
      @end_date,
      '- PRORATED'
order by p.policy_no
Microsoft SQL Server

Avatar of undefined
Last Comment
Patrick Matthews

8/22/2022 - Mon
ASKER CERTIFIED SOLUTION
Thomasian

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
See how we're fighting big data
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
SOLUTION
Patrick Matthews

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
See how we're fighting big data
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
ASKER
Jim Horn

Thomasian - Thanks for the SQL.  That worked great.

>What's the point with using DISTINCT and GROUP BY?
Uhh... identifying myself as a T-SQL amateur??
Patrick Matthews

jimhorn said:
>>Uhh... identifying myself as a T-SQL amateur??

:)
This is the best money I have ever spent. I cannot not tell you how many times these folks have saved my bacon. I learn so much from the contributors.
rwheeler23