Getting count even when empty table is returned in SQL

Question

I am trying to query a db using some limit and offsets for pagination along with some WHERE clause. I also needs the number of total rows that matches the WHERE clause.

I have used "count OVER" in PostgreSQL like this:

SELECT
    user_id,
    created_on,
    COUNT(user_id) OVER() AS full_count
FROM 
    user_info
WHERE 
    user_info.user_id = ANY(unlocked_ids)
ORDER BY 
    created_on DESC
LIMIT p_limit OFFSET p_offset

Now it returns me the number of rows that matches there WHERE clause in the last column. When the offset is too high, it returns empty table as expected. But I need the the number of matches rows all the times, even when the offset is too high.

Currently the solution i tried is making another query with LIMIT 1 and offset 0 which will always return me one result if there is something that matches the WHERE clause. But is it possible to do it without the additional query?

Nimantha · Accepted Answer · 2022-01-08 05:10:14Z

1

Why not just get the count first, once before doing the pagination?

SELECT count(*) as full_count
FROM user_info
WHERE user_info.user_id = ANY(unlocked_ids);

You can then use this count for the rest of the pagination.

This will also save the calculation of the full_count in the query itself. So, the overhead of doing the count first offsets the overhead in the query.

edited Jan 8, 2022 at 5:10

Nimantha

6,5376 gold badges32 silver badges78 bronze badges

answered Oct 16, 2017 at 2:50

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

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

Collectives™ on Stack Overflow

Getting count even when empty table is returned in SQL

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