Menu
I used the following query:
select Patients.LastName,
avg (PatientVisits.Pulse)as pulse,
avg (patientvisits.depressionlevel)as depressionLevel
from Patients
left join PatientVisits
on Patients.PatientKey=PatientVisits.PatientKey
But I get the following error:
Msg 8120, Level 16, State 1, Line 1 Column 'Patients.LastName' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.
891
The GROUP BY clause is normally used along with five built-in, or "aggregate" functions. These functions perform special operations on an entire table or on a set, or group, of rows rather than on each row and then return one row of values for each group.
Aggregate functions are often used with the GROUP BY clause of the SELECT statement.
While all aggregate functions could be used without the GROUP BY clause, the whole point is to use the GROUP BY clause. That clause serves as the place where you'll define the condition on how to create a group. When the group is created, you'll calculate aggregated values.
The GROUP BY statement groups rows that have the same values into summary rows, like "find the number of customers in each country". The GROUP BY statement is often used with aggregate functions ( COUNT() , MAX() , MIN() , SUM() , AVG() ) to group the result-set by one or more columns.
You need to add a GROUP BY to your query:
select Patients.LastName,
avg (PatientVisits.Pulse)as pulse,
avg (patientvisits.depressionlevel)as depressionLevel
from Patients
left join PatientVisits
on Patients.PatientKey=PatientVisits.PatientKey
GROUP BY Patients.LastName
SQL Server requires any columns in your SELECT list that are not in an aggregate function be included in a GROUP BY. Since you are trying to return the Patients.LastName while you are aggregating the data you must include that column in a group by.
142
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
