Postgres count total number of rows under condition, but ignore limit and group by

Question

I've got a query that looks like this:

SELECT t1.id, t1.name, t1.description
FROM t1
LEFT JOIN t2 ON (t2.id = t1.id)
WHERE condition
GROUP BY t1.id
LIMIT 100

I would like to count the total number of rows that satisfy this query past the limit in addition to returning the specified columns (id, name, description). The problem is that due to the GROUP BY, if I were to do a count(*), I just get a bunch of rows with 1.

So my question is: how can I count the number of rows returned by this query? And how can I do so disregarding the limit?

Also is it possible to return both the row count AND the column data in a single query? Or would I need to use two queries?

Lukasz Szozda · Accepted Answer · 2018-11-21 22:01:29Z

1

You could use subquery:

SELECT COUNT(*)
FROM (
  SELECT t1.id
  FROM t1
  LEFT JOIN t2 ON (t2.id = t1.id)
  WHERE condition
  GROUP BY t1.id) sub

EDIT:

Also is it possible to return both the row count AND the column data in a single query? Or would I need to use two queries?

Yes, using windowed COUNT:

SELECT t1.id, COUNT(*) OVER() AS num_of_r0ws
FROM t1
LEFT JOIN t2 ON (t2.id = t1.id)
WHERE condition
GROUP BY t1.id

edited Nov 21, 2018 at 22:01

answered Nov 21, 2018 at 21:57

Lukasz Szozda

181k26 gold badges278 silver badges326 bronze badges

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

3 Comments

Andrew Over a year ago

Thanks for quick answer. But wouldn't your first solution only count 100 rows? I'm looking to count ALL rows that exist disregarding the limit. For example if I had 1000 rows, but the inner query contains a limit 100, I'd still like the count to return 1000

Lukasz Szozda Over a year ago

@Andrew You could remove that limit, I do not see the point of using it at all

Andrew Over a year ago

The limit was for pagination, but I see that your second suggestion actually accomplishes what I want. Thanks for the fast response!

Collectives™ on Stack Overflow

Postgres count total number of rows under condition, but ignore limit and group by

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related