The Old County The Old County - 3 months ago 8
MySQL Question

SQL grouping averages per year

I have an sql query that looks like this

SELECT
`table`.Property AS 'Property',
AVG(`table`.`Value`) AS 'Average Sold Price'
FROM
`table`
WHERE `table`.`Area` LIKE '%NW1%'
GROUP BY `table`.Property
ORDER BY `table`.Property ASC


and this gives a result like so

| Property | Average Sold Price |
| D | 1890895.381275497 |
| F | 528221.9917672797 |
| S | 985241.5226100162 |
| T | 941906.3221196578 |


how would I adapt the query to do an average per year to create an output like this -- there is a coloumn called "Year"

| Property | Avg Sold Price 2016 | Avg Sold Price 2015 | Avg Sold Price 2014 |
| D | 1890895.381275497 | 1690895.381275497 | 1490895.381275497 |
| F | 528221.9917672797 | 518221.9917672797 | 618221.9917672797 |
| S | 985241.5226100162 | 955241.5226100162 | 755241.5226100162 |
| T | 941906.3221196578 | 911901.3221196578 | 781901.3221196578 |

Answer
SELECT 
`table`.Property AS 'Property',
`table`.Year AS 'Year',
AVG(`table`.`Value`) AS 'Average Sold Price'
FROM
`table`
WHERE `table`.`Area` LIKE '%NW1%'
GROUP BY `table`.Property,`table`.Year
ORDER BY `table`.Property ASC
Comments