The following SQL code throws error

Question

    SELECT fp.Physician_Key,
       fp.Month,
       pd.DisplayName,
       hd.ProductName,
       SUM(AmtPaid) AS TotalCost
FROM F_ProgramCost_Fact fp
INNER JOIN D_HEALTHPLANDim hd ON hd.HealthPlan_Key = fp.HealthPlan_Key
INNER JOIN D_PHYSICIANDim pd ON fp.Physician_Key = fp.Physician_Key
INNER JOIN F_MemberPatient_FactLess mpf ON fp.MemberPatientFact_Key = mpf.MemberPatientFact_Key
GROUP BY fp.Physician_Key

Getting this error "Column 'F_ProgramCost_Fact.Month' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause."

Please help! Thanks

As you're `SUM`ming, how you're expecting SQL engine to hangle different values for `F_ProgramCost_Fact.Month` column? And what about the other columns? You have to add them to `GROUP BY` so that you will get the `SUM` by each combination of other columns. — PauloASilva, Aug 18 '16 at 08:52

score 0 · Answer 1 · answered Aug 18 '16 at 08:49

you need to group by all the columns:

select fp.Physician_Key,fp.Month,pd.DisplayName,hd.ProductName,SUM(AmtPaid) as TotalCost
From F_ProgramCost_Fact fp
Inner Join D_HEALTHPLANDim hd
ON hd.HealthPlan_Key = fp.HealthPlan_Key
Inner join D_PHYSICIANDim pd
ON fp.Physician_Key = fp.Physician_Key 
Inner Join F_MemberPatient_FactLess mpf ON fp.MemberPatientFact_Key = mpf.MemberPatientFact_Key
Group By fp.Physician_Key,fp.Month,pd.DisplayName,hd.ProductName

Thanks for helping me out! – Saba Malik Aug 18 '16 at 09:11 — Saba Malik, Aug 18 '16 at 09:11

The following SQL code throws error

1 Answers1