Select Count of Rows in Another Table in a Postgres Select Statement

Select count of rows in another table in a Postgres SELECT statement

SELECT A.*, (SELECT COUNT(*) FROM B WHERE B.a_id = A.id) AS TOT FROM A

Sum of two counts from one table with additional data from another table

Something like this is typically fastest:

SELECT *
FROM   "TABLE_B" b
JOIN  (
   SELECT user_id AS id
        , count(*) FILTER (WHERE col_a)
        + count(*) FILTER (WHERE col_b) AS total
   FROM   "TABLE_A"
   GROUP  BY 1
   ) a USING (id);

While fetching all rows, aggregate first, join later. That's cheaper. See:

• Query with LEFT JOIN not returning rows for count of 0

The aggregate FILTER clause is typically fastest. See:

• For absolute performance, is SUM faster or COUNT?
• Aggregate columns with additional (distinct) filters

---

Often, you want to keep total counts of 0 in the result. You did say:

get the name of each user.

SELECT b.id AS user_id, b.name, COALESCE(a.total, 0) AS total
FROM   "TABLE_B" b
LEFT   JOIN (
   SELECT user_id AS id
        , count(col_a OR NULL)
        + count(col_b OR NULL) AS total
   FROM   "TABLE_A"
   GROUP  BY 1
   ) a USING (id);
...

count(col_a OR NULL) is an equivalent alternative, shortest, and still fast. (Use the FILTER clause from above for best performance.)

The LEFT JOIN keeps all rows from "TABLE_B" in the result.

COALESCE() return 0 instead of NULL for the total count.

---

If col_a and col_b have only few true values, this is typically (much) faster - basically what you had already:

SELECT b.*, COALESCE(aa.ct, 0) + COALESCE(ab.ct, 0) AS total
FROM   "TABLE_B" b
LEFT   JOIN (
   SELECT user_id AS id, count(*) AS ct
   FROM   "TABLE_A"
   WHERE  col_a
   GROUP  BY 1
   ) aa USING (id)
LEFT   JOIN (
   SELECT user_id AS id, count(*) AS ct
   FROM   "TABLE_A"
   WHERE  col_b
   GROUP  BY 1
   ) ab USING (id);

Especially with (small in this case!) partial indexes like:

CREATE INDEX a_true_idx on "TABLE_A" (user_id) WHERE col_a;
CREATE INDEX b_true_idx on "TABLE_A" (user_id) WHERE col_b;

Aside: use legal, lower-case unquoted names in Postgres to make your like simpler.

• Are PostgreSQL column names case-sensitive?

SQL: Get count of rows for values in another column even when those values do not exist

You need a list of the statuses and a left join:

select v.status, count(p.status)
from (values ('Draft'), ('Published'), ('Rejected')
     ) v(status) left join
     products p
     on p.status = v.status
group by v.status;

Select count from another table to each row in result rows

You need to do this -

SELECT C.ClassId, C.ClassName, count(S.StudentId) AS studentCount
FROM CLASSES C LEFT JOIN STUDENTS S ON (C.ClassId=S.ClassId)
GROUP BY C.ClassId, C.ClassName

Count total number of rows where this.row is related with rows in another table

I am trying to count the number of parents who have at least one children.

This should be as simple as:

SELECT COUNT(*) 
FROM people p
WHERE EXISTS (SELECT 1 FROM children c WHERE c.parentid = p.id)

Using EXISTS is usually the most efficient way to check that something, well, exists.

How do you find the row count for all your tables in Postgres

There's three ways to get this sort of count, each with their own tradeoffs.

If you want a true count, you have to execute the SELECT statement like the one you used against each table. This is because PostgreSQL keeps row visibility information in the row itself, not anywhere else, so any accurate count can only be relative to some transaction. You're getting a count of what that transaction sees at the point in time when it executes. You could automate this to run against every table in the database, but you probably don't need that level of accuracy or want to wait that long.

WITH tbl AS
  (SELECT table_schema,
          TABLE_NAME
   FROM information_schema.tables
   WHERE TABLE_NAME not like 'pg_%'
     AND table_schema in ('public'))
SELECT table_schema,
       TABLE_NAME,
       (xpath('/row/c/text()', query_to_xml(format('select count(*) as c from %I.%I', table_schema, TABLE_NAME), FALSE, TRUE, '')))[1]::text::int AS rows_n
FROM tbl
ORDER BY rows_n DESC;

The second approach notes that the statistics collector tracks roughly how many rows are "live" (not deleted or obsoleted by later updates) at any time. This value can be off by a bit under heavy activity, but is generally a good estimate:

SELECT schemaname,relname,n_live_tup 
  FROM pg_stat_user_tables 
ORDER BY n_live_tup DESC;

That can also show you how many rows are dead, which is itself an interesting number to monitor.

The third way is to note that the system ANALYZE command, which is executed by the autovacuum process regularly as of PostgreSQL 8.3 to update table statistics, also computes a row estimate. You can grab that one like this:

SELECT 
  nspname AS schemaname,relname,reltuples
FROM pg_class C
LEFT JOIN pg_namespace N ON (N.oid = C.relnamespace)
WHERE 
  nspname NOT IN ('pg_catalog', 'information_schema') AND
  relkind='r' 
ORDER BY reltuples DESC;

Which of these queries is better to use is hard to say. Normally I make that decision based on whether there's more useful information I also want to use inside of pg_class or inside of pg_stat_user_tables. For basic counting purposes just to see how big things are in general, either should be accurate enough.

Postgres - select rows except where count is more than one and another condition

As it was suggested in the comment, you can use not exists:

select * 
from my_table mt
where mt.appuser_id = 6
and mt.role_id = 'BUSINESS_MANAGER' 
and mt.business_id not in (
  select imt.business_id
  from my_table imt
  where exists (
    select *
    from my_table iimt
    where imt.appuser_id <> iimt.appuser_id
    and iimt.appuser_id = mt.appuser_id
    and iimt.role_id = 'CLIENT_ADMIN' 
  )
)

Filter by count from another table

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);

Updating a column by counting data from another table

Use the proprietary UPDATE ... FROM to perform a join that can be something else than a nested loop:

UPDATE tableA SET tableA.column1 = tbc.count
FROM (SELECT column2,
             count(*) AS count
      FROM tableB
      GROUP BY column2) AS tbc
WHERE tableA.column2 = tbc.column2;

---

Related Topics