PostgreSQL I need to select how many payments each client have

user_18307780 0
Viewed 52

As the title says. It's pretty self explanatory.

Each client have multiple payments and there are tons of them. I need to see how many payments each client have.

select ec.id as client_id , p.id as payment_id
from e_clients ec 
left join payments p on ec.id = p.client_id

I'm guessing it's something I have already done but I can't seem to remember.

I have tried counting but it just returns 1 on each row.

postgresql sql
1 Answers

If you want to perform a count(*) but group before counting (i.e. by client), you can use the group by statement. In your case, this would translate to something along the lines:

SELECT
  ec.id as client_id,
  count(*)
FROM
  e_clients ec 
JOIN
  payments p
ON 
  ec.id = p.client_id
GROUP BY
  ec.id
ORDER BY
  count(*) DESC;
user_24842160
Related