Find all square grids with fewer crashes in 2007 than in 1989?

Go To StackoverFlow.com

0

My nycgrid table with the following schema id,x1,x2,y1,y2. And it looks like following examples:

22,910000,920000,120000,130000
67,930000,940000,170000,180000
171,980000,990000,210000,220000

Another table, nyccrash, contains tuples with information about the car acciddents that occured from 1989 through 2007 (12 thousand records in total). The first attribute is crash_year, followed by type of accident, weather condition...etc and ending in x_coordinate and y_coordinate where the car crash has occurred in NYC.

2007,2,9,4,1,1028977,202232
2004,1,1,1,4,1012600,214101
2003,1,9,1,1,958775,156149
1999,1,1,1,1,997349,175503

This is an extension question to the one I've asked earlier on stackoverflow.

I am trying to find the square grids (Grid ID) such that they have less car crashes occurred in 2007 than in 1989. There are about 100 rows in the nycgrid table. I need only those rows that have had less accidents happened in the year in 2007 than in the year 1989.

And second part, which is probably slightly easier is how could I find the number of car crashes per each square grid (nycgrid.id)? In other words, how display how many crashes occurred on each grid id? Each crash has associated x and y coordinate. Each grid has a x1-x2-y1-y2 coordinates that make up a square.

2012-04-05 21:20
by Sahat Yalkabov


2

Starting with RBarryYoung's answer to your last question we just pivot on the year using SUM/CASE and then compare the values

SELECT ID
FROM

    (SELECT  
                grid.ID, 
                SUM(CASE WHEN yearCol = 1989 THEN 1 ELSE 0 END) CrashCount_1989,
                SUM(CASE WHEN yearCol = 2007 THEN 1 ELSE 0 END) CrashCount_2007
    FROM        crashes
    INNER JOIN  grid    
                ON  crashes.x_coordinate BETWEEN grid.x1 AND grid.x2
                And crashes.y_coordinate BETWEEN grid.y1 AND grid.y2
    WHERE       crashes.yearCol IN(1989, 2007)
    GROUP BY    grid.ID) t
WHERE CrashCount_2007 < CrashCount_1989
2012-04-05 21:29
by Conrad Frix
Ads