PostgreSQL: Get the count of rows in a join query

Question

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

Thorsten Kettner · Accepted Answer · 2018-03-29 09:46:26Z

1

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.)

answered Mar 29, 2018 at 9:46

Thorsten Kettner

96.6k8 gold badges56 silver badges82 bronze badges

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

2 Comments

Geo Thomas Over a year ago

I need to join cbrain_school since I need only users who are part of that table .

Thorsten Kettner Over a year ago

So it's as I supposed; you don't really want to get data from the table, but you want to limit your result to users that are in that table. Limiting a queries result best happens in the WHERE clause. So you'd use EXISTS or IN to check this in your WHERE clause as shown. This may also remove duplicates in some situations (e.g. if it were possible for a user to be in multiple cbrain_school records).

Collectives™ on Stack Overflow

PostgreSQL: Get the count of rows in a join query

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related