Multi-table count() in Postgres

Question

I have a table events which has id and venue columns.

The events.venue column matches the venues.id column, a fairly typical and simple setup.

Some of the venues are 'broken' in that they are missing critical pieces of information, such as geo_lat. I am trying to create a priority list of broken venues, with the venue that is hosting the most events at the top, so that we know which venues are most important to fix.

Myquery so far looks like;

select x.venue_id as id, count(x.venue_id) as events 
from (
    select * 
    from events e, venues v 
    where e.venue=v.id and v.geo_lat is null 
    group by e.venue
) as x order by events desc

Which is giving the error: ERROR: column "e.id" must appear in the GROUP BY clause or be used in an aggregate function

The output I am trying to achieve would look something like;

venue.id | events
---------|-------
      12 |    219
     214 |    141
      36 |     41
    1834 |     22
     931 |      4

Containing just the broken venues. That way, I know that fixing venue 12 is top priority and so on.

With questions like this where you're showing your original queries, it can be really helpful to show CREATE TABLE statements and sample data INSERTs, along with the expected output you helpfully showed. It makes it much easier to test queries and means folks writing answers don't have to make their own dummy tables and data. — Craig Ringer
– Craig Ringer, Commented Oct 16, 2012 at 12:07

MatBailie · Accepted Answer · 2012-10-16 12:02:54Z

5

SELECT
  venue.id,
  COUNT(events.venue)    AS event_count
FROM
  venues
LEFT JOIN
  events
    ON events.venue = venue.id
WHERE
  venue.geo_lat IS NULL
GROUP BY
  venue.id
ORDER BY
  COUNT(events.venue) DESC

The LEFT JOIN allows for venues with no events. (INNER JOIN would filter them out.)

answered Oct 16, 2012 at 12:02

MatBailie

87.5k19 gold badges112 silver badges144 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Multi-table count() in Postgres

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related