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:
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