CODEWITHSUNDEEP
sql
TszHin Ma
TszHin Ma

Reputation: 197

sql - counting result in two table

I have two table, the first one contains the user data and the second one contains user wanted item list.

Select * FROM user
id | name
 1 | tom
 2 | may
 3 | peter

Select * FROM wantedlist
userid | item
  1    |  pen
  1    |  bag
  3    |  pen

I want to get the table which contains the amount of user wanted items. What I want:

id | name | totalitems
 1 | tom  |     2
 2 | may  |     0
 3 | peter|     1

I tried to join two table to get the table but failed. What I tried and got:

id | name | totalitems
 1 | tom  |     2
 3 | peter|     1

The result does not contain who did not have wanted items whether using left, right, inner join. Can someone tell me what should I use?

Upvotes: 1

Views: 42

Answers (1)

Giorgos Betsos
Giorgos Betsos

Reputation: 72225

You need to LEFT JOIN user table to wanted list table, then perform a GROUP BY and COUNT:

SELECT u.id, COUNT(w.item)
FROM user AS u
LEFT JOIN wantedlist AS w ON u.id = w.userid
GROUP BY u.id

Demo here

Upvotes: 2

Related Questions