/*** 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.