0

I need a MySQL query statement that returns the average sale price for properties that were ‘under contract’ in the past month for each State where the agency operates by property type. (there are just 5 different types of property in the whole country. so for now there are just 3 states)

so, the query should return a table like the one attached below:

enter image description here

I have done this, Without adding the Type of property (Which runs well)

SELECT State, AVG(List_Price) AS 'Average sale price' 
FROM PROPERTY 
WHERE Property_Status = 'under contract' 
  AND Date_Edited >= DATE_SUB(CURDATE(), INTERVAL 1 MONTH) 
GROUP BY State ORDER BY AVG(List_Price) DESC;

But when I add the type of property, mi code does not work

SELECT State Property_Type, Property_Status, AVG(List_Price) AS 'Average 
sale price' 
FROM PROPERTY
WHERE Property_Status = 'under contract' 
  AND Date_Edited >= DATE_SUB(CURDATE(), INTERVAL 1 MONTH) 
GROUP BY Property_Type;

I got this I

20:56:03 Property_Status = 'under contract' AND Date_Edited >= DATE_SUB(CURDATE(), INTERVAL 1 MONTH) GROUP BY Property_Type LIMIT 0, 1000 Error Code: 1055. Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'abc_real_estate.PROPERTY.State' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by 0.00062 sec

REALLY APPRECIATE YOUR HELP

  • 2
    Does this answer your question? [SELECT list is not in GROUP BY clause and contains nonaggregated column .... incompatible with sql\_mode=only\_full\_group\_by](https://stackoverflow.com/questions/41887460/select-list-is-not-in-group-by-clause-and-contains-nonaggregated-column-inc) Please search this site for the error message before posting in the future, as chances are quite good that the problem has been asked about here before. – Ken White Apr 30 '22 at 01:58

0 Answers0