Hi all,

​

so for context, I'm new-ish to learning SQL and am using a postgres database to keep track of sessions conducted as a personal trainer. I have a table for purchases, clients, and sessions.

​

Currently the code I have written is:

​

SELECT count(*) as completed, clients.client_id, purchases.purchase_id, purchases.session_count, purchases.purchase_date
from clients
inner join purchases 
on clients.client_id = purchases.client_id
inner join sessions
on sessions.client_id = purchases.client_id
group by purchases.purchase_id, clients.client_id;

Previously I got the output I expected. Basically trying to see the number of completed sessions for each purchase/package sold compared to the number of sessions purchased. BUT...

Now that client #6 as two purchases (purchase id 5 and 6), I still want to see the same results, but its not working.

​

The output is saying that there's 5 rows for both purchase id 5 and 6, when if I run smaller queries to verify, there should be 4 rows with purchase id 5, and one row with purchase id 6. So, I'm guessing it's something to do with the group by statement.

​

I know it's probably something small that in missing conceptually, so any guidance to understanding what I'm missing is appreciated.

​

**EDIT**

okay so when I do

select count(*), purchase_id from sessions
group by purchase_id;

I get the correct answer. its only when I join with the purchases table and then try to do something like this:

select count(*), purchases.purchase_id from sessions
inner join purchases
on sessions.client_id = purchases.client_id
group by purchases.purchase_id;

that it counts 5 rows for both purchase id 5 and 6, so its not separating the two somehow.

​

**EDIT OF THE EDIT**

so if I add:

where purchases.purchase_id = sessions.purchase_id

before the group by I get the correct answer. Upon further investigation, it looked like there were weird matches and additional rows where client id was 6 but purchase id was 5 OR 6 after the join was done. So i think i got it!

Still open to recommendations/explanations of exactly WTF happened here haha