PostgreSQL: Get the count of rows in a join query

I am trying to get some data joining few tables. I have an audit table where I store the audits for actions performed by users. I am trying to get the list of users in the order of the number audits they have and the number of audits. I have the following query:

SELECT s.user_created, 
u.first_name, 
u.last_name, 
u.email, 
a.message as audits 
FROM cbrain_school s
inner join ugrp_user u on s.user_created = u.user_id 
inner join audit a on u.user_id = a.user_id 
order by u.created_time desc;

This query will give me 1 row per entry in the audit table. I just want 1 row per user and the count of entries in the audit table ordered by the number of audits.

Is there any way to do that. I was getting an error when I tried to include count() in the above query

Solution

First of all you are joining with the table cbrain_school. Why? You are selecting no data from this table (except for s.user_created which is simply u.user_id). I suppose you want to limit the users show to the cbrain_school.user_created? Then use EXISTS or IN to look this up.

select u.user_id, u.first_name, u.last_name, u.email, a.message as audits
from ugrp_user u
inner join audit a on u.user_id = a.user_id
where u.user_id in (select user_created from cbrain_school)
order by u.created_time desc;

This shows much better that cbrain_school.user_created is mere criteria. (But the query result is the same, of course.) It's a good habit to avoid joins, when you are not really interested in the joined rows.

Now you don't want to show each message anymore, but merely count them per user. So rather then joining messages, you should join the message count:

select u.user_id, u.first_name, u.last_name, u.email, a.cnt
from ugrp_user u
inner join
(
  select user_id, count(*) as cnt
  from audit
  group by user_id
) a on u.user_id = a.user_id
where u.user_id in (select user_created from cbrain_school)
order by u.created_time desc;

(You could also join all messages and only then aggregate, but I don't recommend this. It would work for this and many other queries, but is prone to errors when working with multiple tables, where you might suddenly count or add up values multifold. It's a good habit to join before aggregating.)