CODEWITHSUNDEEP
sqlhive
CustomNet
CustomNet

Reputation: 652

SQL - Insert using Column based on SELECT result

I currently have a table called tempHouses that looks like:

avgprice | dates | city

dates are stored as yyyy-mm-dd

However I need to move the records from that table into a table called houses that looks like:

city | year2002 | year2003 | year2004 | year2005 | year2006

The information in tempHouses contains average house prices from 1995 - 2014.

I know I can use SUBSTRING to get the year from the dates:

SUBSTRING(dates, 0, 4)

So basically for each city in tempHouses.city I need to get the the average house price from the above years into one record.

Any ideas on how I would go about doing this?

Upvotes: 0

Views: 145

Answers (2)

Kevin Hogg
Kevin Hogg

Reputation: 1781

This is an SQL Server approach, and a PIVOT may be a better, but here's one way:

 SELECT City,
        AVG(year2002) AS year2002,
        AVG(year2003) AS year2003,
        AVG(year2004) AS year2004
   FROM (
         SELECT City,
                CASE WHEN Dates BETWEEN '2002-01-01T00:00:00' AND '2002-12-31T23:59:59' THEN avgprice
                     ELSE 0
                END AS year2002,
                CASE WHEN Dates BETWEEN '2003-01-01T00:00:00' AND '2003-12-31T23:59:59' THEN avgprice
                     ELSE 0
                END AS year2003
                CASE WHEN Dates BETWEEN '2004-01-01T00:00:00' AND '2004-12-31T23:59:59' THEN avgprice
                     ELSE 0
                END AS year2004
                -- Repeat for each year
        )
  GROUP BY City

The inner query gets the data into the correct format for each record (City, year2002, year2003, year2004), whilst the outer query gets the average for each City.

There many be many ways to do this, and performance may be the deciding factor on which one to choose.

Upvotes: 1

Justin Mitchell
Justin Mitchell

Reputation: 304

The best way would be to use a script to perform the query execution for you because you will need to run it multiple times and you extract the data based on year. Make sure that the only required columns are city & row id:

http://dev.mysql.com/doc/refman/5.0/en/insert-select.html

INSERT INTO <table> (city) VALUES SELECT DISTINCT `city` from <old_table>;

Then for each city extract the average values, insert them into a temporary table and then insert into the main table.

SELECT avg(price), substring(dates, 0, 4) dates from <old_table> GROUP BY dates;

Otherwise you're looking at a combination query using joins and potentially unions to extrapolate the data. Because you're flattening the table into a single row per city it's going to be a little tough to do. You should create indexes first on the date column if you don't want the database query to fail with memory limits or just take a very long time to execute.

Upvotes: 0

Related Questions