I want to join the result of two different queries from two different tables based on a common column (which is named differently in the two tables) but I cant seem to figure out the easy way to do it. I'm using psycopg2 on python 3.5.

From the first table, I need the Count of meetings. So I do:

SELECT   user_id,COUNT(1) as num
FROM     tasks
WHERE    created_at BETWEEN %s and %s

This gives me

UserID | Number
1      |  123
2      |  12

UserID is my mutual column. Now I made userID into a list by list comprehension, then I do:

SELECT id, given_name,family_name,email FROM users WHERE id in +str(userID))

and I get the id, given_name, family_name, and email fields from the second table.

Now I need to add the Number column into this result, which I'm doing by very inefficient non-DB means (pandas library in python)

I'm pretty sure this entire thing can be done in one query (perhaps with union?), but I cant seem to work it out.

Intended output:

id | given_name | family_name | email | Number
  • Please show what the output should look like, as that's not entirely clear from your description.
    – Andriy M
    Jul 20, 2016 at 9:39
  • Added, but i was using a guest account hence now it's pending peer edit
    – Wboy
    Jul 20, 2016 at 9:53
  • Feel free to request an account merge for your two accounts so that you can restore your full control over your question.
    – Andriy M
    Jul 20, 2016 at 9:57

1 Answer 1


You can do it with:

SELECT   u.id, u.given_name, u.family_name, u.email, COUNT(1) as num
FROM     users u JOIN tasks t
          ON (u.id=t.user_id)
WHERE    t.created_at BETWEEN %s and %s
GROUP BY u.id, u.given_name, u.family_name, u.email, (DAYS_AGO_7_CONFIG,YESTERDAY))

To address the question in the comments, you can add a HAVING clause after GROUP BY to limit the results to those where num is greater than 5:

  • THANK YOU! i would mark you correct, but i was using a guest account just now =/ The WHERE clause in the 3rd line was unneeded as the ON did that, but is there a way to filter based on the num being > 5? i get num does not exist
    – Wboy
    Jul 20, 2016 at 9:52
  • @Wboy Changed my answer.
    – Marco
    Jul 20, 2016 at 9:57

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.