Count NULL as 0?

[lurtnowski]

I have this query

SELECT COUNT(device_id) AS count,devices.created_by,users.name
			FROM devices
			INNER JOIN users ON devices.created_by = users.email
			GROUP BY created_by

which returns three users.
Problem is, I have 5 users (two of them haven’t done anything)
Can I get a query which would also include them to show that they have done nothing?

[sibertius]

@lurtnowski

which returns three users.
Problem is, I have 5 users (two of them haven’t done anything)
Can I get a query which would also include them to show that they have done nothing?

coalesce(device_id,0) converts NULL to 0

[m_hutley]

@lurtnowski

Can I get a query which would also include them to show that they have done nothing?

Don’t inner-join when you want all of the results from one of the tables.

[r937]

@m_hutley

Don’t inner-join when you want all of the results from one of the tables.

that’s harsh, man

you’re going to make him think about which table

harsh, i say

[lurtnowski]

oh yesm this is what a RIGHT JOIN is for, right?

SELECT COUNT(device_id) AS count,devices.created_by,users.name
			FROM devices
			RIGHT JOIN users ON devices.created_by = users.email
			GROUP BY created_by

cause it only returns 4 users

[lurtnowski]

oh, it worked if I GROUP BY users.name

[r937]

dear mr/mrs/miss/ms lurtnowski (as the case may be), you need to do some testing on your own before coming back here

right join? left join? WHAT’S THE DIFFERENCE!!

also, decide what column you should actually be grouping on

test all these out yourself, please

[r937]

ah, i see you figured it out while iwas replying

well done