SQL obtaining an average sale cost over multiple years

Question

I have an sql query that will return the average sold price for a given property type at a particular year.

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

the output looks like this

| Property | Year | Average Sold Price
|    F     | 2011 | 440242.18137204903

on a look up at 2016

| Property | Year | Average Sold Price
|    F     | 2016 | 702453.9544754727

-- how would I alter the query to get a merged output - so something like this, try to reduce the number of calls etc..

| Property | Average Sold Price (2011) | Average Sold Price (2016)
|    F     | 440242.18137204903        | 702453.9544754727

it would be slower to get it as one row but if you remove `AND table.year='2011'` you'll get all years in multiple rows — Cfreak, Aug 24 '16 at 14:42
The feature you need is a pivot - http://stackoverflow.com/questions/7674786/mysql-pivot-table — Neville Kuyt, Aug 24 '16 at 14:43
WHERE year IN(2011,1016) GROUP BY year ... as stated previously, handle the display issue in the presentation layer — Strawberry, Aug 24 '16 at 14:44

score 0 · Answer 1 · answered Aug 24 '16 at 16:00

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

^ this has produced a usable output.

| Property | Year | Average Sold Price
|    F     | 2011 | 440242.18137204903
|    F     | 2016 | 702453.9544754727

SQL obtaining an average sale cost over multiple years

1 Answers1