Filter by count from another table

Question

This query works fine only without WHERE, otherwise there is an error:

column "cnt" does not exist

SELECT
  *,
  (SELECT count(*)
   FROM B
   WHERE A.id = B.id) AS cnt
FROM A
WHERE cnt > 0

To get the best solution, you would need to disclose exact table definitions, your version of Postgres and what the query should return *exactly* Do you need *all* columns of `A` and the corresponding count in `B`? Or just some columns - or even just the ID? - and whether ate least one related row exists `B`? — Erwin Brandstetter, Oct 15 '17 at 00:56

score 3 · Accepted Answer · answered Oct 14 '17 at 14:00

3

Use a subquery:

SELECT a.*
FROM (SELECT A.*,
             (SELECT count(*)
              FROM B
              WHERE A.id = B.id
             ) AS cnt
      FROM A
     ) a
WHERE cnt > 0;

Column aliases defined in the SELECT cannot be used by the WHERE (or other clauses) for that SELECT.

Or, if the id on a is unique, you can more simply do:

SELECT a.*, COUNT(B.id)
FROM A LEFT JOIN
     B
     ON A.id = B.id
GROUP BY A.id
HAVING COUNT(B.id) > 0;

Or, if you don't really need the count, then:

select a.*
from a
where exists (select 1 from b where b.id = a.id);

answered Oct 14 '17 at 14:00

Gordon Linoff

1,242,037
58
646
786

To your second query: postgres really allows to have outside aggregate function attributes that are not behind group by? – Radim Bača Oct 14 '17 at 14:23
@RadimBača . . . Yes. Postgres implements the ANSI standard (optional) functionality that -- essentially -- when a column is declared as unique then the rest of the columns are not needed. Here is a SQL Fiddle: http://www.sqlfiddle.com/#!15/f5859/1. – Gordon Linoff Oct 14 '17 at 14:29
@Gordon: That's almost, but not quite true. Works only for PK column(s) in Postgres 9.1 or later. In particular, it does *not* work for UNIQUE or EXCLUSION constraints. Extended fiddle: http://www.sqlfiddle.com/#!15/5dcbf/1 – Erwin Brandstetter Oct 15 '17 at 01:29
@ErwinBrandstetter . . . Thank you for the clarification. Clearly, I've only ever done it on primary keys. – Gordon Linoff Oct 15 '17 at 04:03

Erwin Brandstetter · Answer 2 · 2017-10-15T01:37:50.863

Assumptions:

You need all columns from A in the result, plus the count from B. That's what your demonstrated query does.
You only want rows with cnt > 0. That's what started your question after all.
Most or all B.id exist in A. That's the typical case and certainly true if a FK constraint on B.id references to A.id.

Solution

Faster, shorter, correct:

SELECT *  -- !
FROM  (SELECT id, count(*) AS cnt FROM B) B
JOIN   A USING (id)  -- !
-- WHERE cnt > 0 -- this predicate is implicit now!

Major points

Aggregate before the join, that's typically (substantially) faster when processing the whole table or major parts of it. It also defends against problems if you join to more than one n-table. See:
- Aggregate functions on multiple joined tables
You don't need to add the predicate WHERE cnt > 0 any more, that's implicit with the [INNER] JOIN.
You can simply write SELECT *, since the join only adds the column cnt to A.* when done with the USING clause - only one instance of the joining column(s) (id in the example) is added to the out columns. See:
- How to drop one join key when joining two tables

Your added question in the comment

postgres really allows to have outside aggregate function attributes that are not behind group by?

That's only true if the PK column(s) is listed in the GROUP BY clause - which covers the whole row. Not the case for a UNIQUE or EXCLUSION constraint. See:

Return a grouped list with occurrences using Rails and PostgreSQL

SQL Fiddle demo (extended version of Gordon's demo).

Filter by count from another table

2 Answers2

Assumptions:

Solution

Major points

Your added question in the comment