CODEWITHSUNDEEP
sqljoin
steaksauce
steaksauce

Reputation: 37

SQL combine 2 separate queries and show total

I have 3 tables. Users, Things, Objects.

Users can add both things and objects. I want to count how many things, objects, and also total of both users have added. I have it started but having trouble bringing the second table in. http://sqlfiddle.com/#!9/bf6927/9

Objects Table:

id, added_by, object_name

Things Table:

id, added_by, thing_name

Users Table:

id, username

Ideally the results would look like this:

Username - Total Objects Added - Total Things Added - Total of Both

Upvotes: 2

Views: 33

Answers (1)

Fahmi
Fahmi

Reputation: 37473

You can try the below - DEMO Here

SELECT username, coalesce(objects,0) as objects, coalesce(things,0) as things,   
coalesce(objects,0)+coalesce(things,0) as total
FROM users left join
(
   select user_id, count(*) as objects from table1
   group by user_id
)t1 on t1.user_id = users.id
left join 
(
  select user_id, count(*) as things from table2 
  group by user_id
)t2
on t1.user_id=t2.user_id

Upvotes: 1

Related Questions