Count number of records

Databases
1

I have a table

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
2 
SELECT user
     , COUNT(*) AS records
  FROM devices
GROUP
    BY user
3 Likes
3

I modified it a little to show the number of records created by users

SELECT COUNT(*) AS records, created_by FROM devices GROUP BY created_by

and the result is as expected

image
image798×438 24.7 KB

But I want to account for the user (from the users table)
image
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
image
image1070×274 13.3 KB

but shouldn’t it show the other user?

4

your guess is correct

presumably all your years of writing SQL will also tell you what kind of a join to use – INNER, LEFT, RIGHT, or FULL

in case you’re not sure, just do a simple google on “what’s the difference between INNER, LEFT, RIGHT, and FULL joins?”

5

  1. As I know, standard column name has no prefix. Exception: foreign key has its table as prefix.

  2. “user” or “created_by” should be replaced exactly woth foreign key “user_id”.

So, you should have a two tables: devices[id, user_id…] and users[id, name, email…].

Your first query…

SELECT users.*, IFNULL(COUNT(*), 0) as records_match
FROM users
LEFT JOIN devices
ON users.id = devices.user_id
GROUP BY users.id
6

dear @igor_g, there are two errors in your query

first, your grouping is wrong – you can’t use the dreaded evil “select star” in the SELECT and then have only one column in the GROUP BY

second, using COUNT(*) in a left outer join will never produce a NULL result, and it won’t produce a 0 result either

7

never kne wthe difference between the joins, So I did a right join as I wanted it to inculde all of the right table
and

image
image1211×273 14 KB

it worked, that was pretty simple.

8

  1. No. All fields will correctly selected.

  2. Yes. Correct query is…

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

…that’s why.

9

no they won’t

please read https://dev.mysql.com/doc/refman/5.6/en/group-by-handling.html

correct query is

SELECT users.id
     , COUNT(devices.id) AS records_match
  FROM users
LEFT 
  JOIN devices
    ON devices.user_id = users.id
GROUP 
    BY users.id
10

Yes, they will. I have checked it right now in my DB.

That is also variant.

11

what version are you on?

12

10.1.9-MariaDB

13

i’m not familiar with release changes, but that release is 5 years old

according to the GROUP BY page…

so this is the same as MySQL

the only thing i don’t know is which release in MariaDB that ONLY_FULL_GROUP_BY becomes the default

obviously, after your version

14

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.

15

This topic was automatically closed 91 days after the last reply. New replies are no longer allowed.