CREATE TABLE devices (
device_id INT autonumber,
user VARCHAR(50),
...
)
with about 75 records in it
I’m trying to create a query that gives me the number of records for each user like
Carl, 7
Tom, 25
John, 23
…
Am I looking for something like
SELECT COUNT(device_id), DISTINCT user FROM devices
But I want to account for the user (from the users table)
if they have been not productive and didn’t enter any records
So Id lime to also have the user jdoe@industechnologies.com has entered 0 records
Im guessing a JOIN would be used
SELECT users.*, SUM(IF(devices.id IS NULL, 0, 1)) as records_match
FROM users
LEFT JOIN devices
ON users.id = devices.user_id
GROUP BY users.id
I 've mean some another query…
SELECT users.*, IFNULL(records, 0) as records_match
FROM users
LEFT JOIN
(SELECT user_id, COUNT(*) as records FROM devices GROUP BY user_id) as dev
ON users.id = dev.user_id
Sorry, but not. I have set this mode in my DB, but query with * shows all fields.
I think, if DB has * by group, it takes all this fields from first record of any group. And if “group by” criteria is primary key, then we have just record with this key.