Count Function after query in postgres sql

Question

I have written some query and I wanted to know the number of rows or total rows count. Is there a "count" function that can be applied on top of the query?

My query is like:

select customer_id, sum(amount)
from payment
group by customer_id having sum(amount)>100;

Laurenz Albe · Accepted Answer · 2020-11-20 14:07:11Z

1

You could use count() as a window function, like in

SELECT *,
       count(*) OVER () AS total_count
FROM (SELECT customer_id,
             sum(amount)
      FROM payment
      GROUP BY customer_id HAVING sum(amount)>100) AS q;

That will return the total count in an additional column.

answered Nov 20, 2020 at 14:07

Laurenz Albe

257k22 gold badges312 silver badges388 bronze badges

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

2 Comments

forpas Over a year ago

There is no need for the subquery. COUNT() can be used as the 3d column in the main query.

Laurenz Albe Over a year ago

@forpas I am trying to show a general principle. You are right in this case, but if the query contains LIMIT or OFFSET, a subquery would be necessary.

Gordon Linoff · Accepted Answer · 2020-11-20 13:46:15Z

1

You can use a subquery or CTE:

select count(*)
from (select customer_id, sum(amount)
      from payment
      group by customer_id
      having sum(amount)>100
     ) c;

answered Nov 20, 2020 at 13:46

Gordon Linoff

1.3m62 gold badges705 silver badges857 bronze badges

Collectives™ on Stack Overflow

Count Function after query in postgres sql

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related