CODEWITHSUNDEEP
sqlpostgresql
keviv
keviv

Reputation: 21

Count Function after query in postgres sql

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;

Upvotes: 1

Views: 275

Answers (2)

Laurenz Albe
Laurenz Albe

Reputation: 246228

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.

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269543

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;

Upvotes: 1

Related Questions