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