0

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
GROUP BY user_id """,(DAYS_AGO_7_CONFIG,YESTERDAY))

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 '16 at 9:39
  • Added, but i was using a guest account hence now it's pending peer edit – Wboy Jul 20 '16 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 '16 at 9:57
2

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:

HAVING   COUNT(1)>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 '16 at 9:52
  • @Wboy Changed my answer. – Marco Jul 20 '16 at 9:57

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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