Group by SQL statement

Question

So, I got this statement that works fine:

SELECT MAX(patient_history_date_bio) AS med_date, medication_name FROM biological WHERE patient_id = 12) GROUP BY medication_name 

But I would also like to have the appropriate medication_dose. Therefore i type this

 SELECT MAX(patient_history_date_bio) AS med_date, medication_name, medication_dose FROM biological WHERE (patient_id = 12) GROUP BY medication_name 

But this gives me an error:

"coumn" bio.medication_dose "is not allowed in the select list because it is not contained in the aggregate function or in the GROUP BY clause."

So I try to add medication_dose to the GROUP BY clause, but then it gives me extra lines that I don't need. I would like to get the last row for each medicine in my table. (The last line is determined by the max function, getting the last date).

How to fix this problem?

Answer 1

Using:

 SELECT b.medication_name, b.patient_history_date_bio AS med_date, b.medication_dose FROM BIOLOGICAL b JOIN (SELECT y.medication_name, MAX(y.patient_history_date_bio) AS max_date FROM BIOLOGICAL y GROUP BY y.medication_name) x ON x.medication_name = b.medication_name AND x.max_date = b.patient_history_date_bio WHERE b.patient_id = ? 

Answer 2

If you really need one quick workaround, you can apply the aggregate function to your medication_dose , like MAX(medication_dose) .

However, note that this usually indicates that you are either creating the query incorrectly or you need to reorganize / normalize the database schema. In your case, it looks like you are processing the request incorrectly. The correct approach should be suggested by OMG Poinies in another answer .

You may be interested in learning the following interesting article, which describes the causes of this error:

• But WHY Should this column be included in the aggregation function or GROUP BY clause?

Answer 3

You need to put max(medication_dose) in your choice. Group by returns a result set that contains different values ​​for the fields in the group by clause, so apparently you have several records that have the same medication_name but different doses, so you get two results.

By inserting max(medication_dose) , it will return the maximum dose for each medication_name . You can use any aggregate function for the dose (max., Min., Avg, sum, etc.).