Get geometry column back when counting points in polygon using PostGIS

Question

I have two PostGIS tables: earthquakes and borders, which contain point data on earthquake locations, and polygons of countries in the world respectively.

I want to try and count the earthquakes within each country, and I've managed to do this using the following SQL:

SELECT ch01.borders.name, COUNT(ch01.earthquakes.geom) as count FROM ch01.borders, ch01.earthquakes
WHERE ST_Contains(ch01.borders.geom, ch01.earthquakes.geom)
GROUP BY ch01.borders.name ORDER BY count DESC;

This gives me a nice ordered table showing the number of earthquakes in each country. However, I'd like to produce a map of the world in QGIS, with each country shaded relative to the number of earthquakes occurring within its borders - but to do that, I need to get a geometry column back from the SQL query (it seems that I'll also need a primary key, but I'll cross that bridge later).

I tried simply selecting the geometry column in the SQL:

SELECT ch01.borders.geom, ch01.borders.name, COUNT(ch01.earthquakes.geom) as count FROM ch01.borders, ch01.earthquakes
WHERE ST_Contains(ch01.borders.geom, ch01.earthquakes.geom)
GROUP BY ch01.borders.name ORDER BY count DESC;

but that gives me an error saying:

ERROR: column "borders.geom" must appear in the GROUP BY clause or be used in an aggregate function

The problem is: I don't want to aggregate or group by the geometry, I just want to return the geometry for the rows that I've got. How can I do this?

Answer 1

One way is to use a WITH query, also known as a Common Table Expression. You can use the first query you wrote (lightly modified) in the WITH query and then join it to your "borders" table. Assuming the PRIMARY KEY for "borders" is "gid", it could look like this:

WITH eqs_per_border AS (
  SELECT
    ch01.borders.gid AS gid,
    COUNT(*) AS eq_count
  FROM
    ch01.borders,
    ch01.earthquakes
  WHERE ST_Contains(ch01.borders.geom, ch01.earthquakes.geom)
  GROUP BY ch01.borders.name 
)
SELECT
  ch01.borders.gid AS "ROWID",
  ch01.borders.name AS border_name,
  eqs_per_border.eq_count AS eq_count,
  ch01.borders.geom AS geom
FROM 
  ch01.borders,
  eqs_per_border
WHERE ch01.borders.gid = eqs_per_border.gid
ORDER BY count DESC

A couple of notes: First, use a JOIN on the second query if that feels more natural. Second, it is not necessary to put the "ROWID" alias on the "gid" column. It's just a habit I developed from a trick to get QGIS to respect SpatiaLite views.