Avatar of RUA Volunteer2?
RUA Volunteer2?
Flag for United States of America

asked on 

Summarizing 3 columns & getting Aggregate function and Group By error.

/*** This was completely mock up of a query that needs only to sum three columns below. I took out a lot and may have missed some of the basic and commonly known query formats. Please ignore those.  
The problem is that I am getting the dreaded aggregate function and or group by issue. See Below. 
My focus is what do I need to do to make this sum of the 3 columns work correctly? 
It would be great to know how to always keep that function group by error from happening?
 
With source1
As
(
select 
       ROW_NUMBER() Over(Partition by f.freq_key Order By req._request_key desc,req._updated desc) As Row_Num1,
       p.person_id,
       p.first_name,
       p.last_name,
       p.birth_date,
       f.ded_part,
       f.coin_part,
       req.co_pay,
   Sum(f.ded_part + f.coin_part + req.co_pay) as Totals,
       
from c_request req 
       join c_frequency f on req._request_key = f.req_key
join c_person p on p.person_key = f.person_key
 
where 1=1
and req.payer_id='Benefit_Me_For_A_Change_Insurance Co.'
and convert(date,req.completed_date) between '2021-04-01' and '2021-04-30'
)
select
       person_id,
       first_name,
       last_name,
       birth_date,
       ded_part,
       coin_part,
       ISNULL(co_pay,0) as co_pay,
        Totals,
       
from
       source1
       where Row_Num1 = 1
 
  Order By p.ssn_number
 
When I execute this I get:
Msg 8120, Level 16, State 1, Line ??
Column 'table.one_of_the_key' columns is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause. I did not define the actual column that was in error as this query is much bigger actually. I hope this gives the context of the problem.
Microsoft SQL Server

Avatar of undefined
Last Comment
Qlemo

8/22/2022 - Mon