0

I have table that does something like this

+--------------------------+--------+------+---------+
|                          | City   | Year | Density |
+--------------------------+--------+------+---------+
| Project 1                | City A | 2008 | 500     |
+--------------------------+--------+------+---------+
| Project 2                | City B | 2012 | 800     |
+--------------------------+--------+------+---------+
| Project 3                | City C | 2012 | 400     |
+--------------------------+--------+------+---------+
| Project 4                | City A | 2008 | 600     |
+--------------------------+--------+------+---------+
| Project 5                | City C | 2013 | 700     |
+--------------------------+--------+------+---------+
| etc (c. 30,000 projects spread across 30 cities)   |
+--------------------------+--------+------+---------+

(About 30,000 projects spread across 30 cities.)

I can write a query like:

SELECT Year, AVG(`Density`) as Density FROM table  where City=’A’ GROUP BY Year

Which works fine for one city. Could anyone point me in the right direction as to how I write a single query that would calculate the average by year for each city? I’d anticipate a results table that looked something like this:

+------+--------+--------+--------+-------------+
|      | City A | City B | City C | City D, etc |
+------+--------+--------+--------+-------------+
| 2005 |        |        |        |             |
+------+--------+--------+--------+-------------+
| 2006 |        |        |        |             |
+------+--------+--------+--------+-------------+
| 2008 |        |        |        |             |
+------+--------+--------+--------+-------------+
| 2009 |        |        |        |             |
+------+--------+--------+--------+-------------+
| 2010 |        |        |        |             |
+------+--------+--------+--------+-------------+
| etc  |        |        |        |             |
+------+--------+--------+--------+-------------+

I have tried to use a subquery in the where clause (where in (select distinct City)) but that did not behave as I expected.

Or do I just have to do a separate line for each of the 30 cities by hand?

I am no expert with MySQL and can't see conceptually what I need to do. If anyone could give me any pointers I would be very grateful. Thanks.

O. Jones
  • 103,626
  • 17
  • 118
  • 172
user3677778
  • 3
  • 2
  • 1
    Possible duplicate of [MySQL pivot table](https://stackoverflow.com/questions/7674786/mysql-pivot-table) – Andreas Feb 05 '18 at 22:05
  • This kind of thing is called a pivot table: it pivots a result set so stuff that generally appears on rows will appear on columns instead. You can look it up. It's a notorious pain in the neck in MySQL, unfortunately. – O. Jones Feb 05 '18 at 22:06

1 Answers1

0

You can group by multiple columns:

SELECT city, year, AVG(density) AS density
FROM table
GROUP BY city, year

This will return a separate row for each city/year combination. To get cities as columns, you'll need to pivot it. See MySQL pivot table

Barmar
  • 741,623
  • 53
  • 500
  • 612