Count separate row on same id from different table : MySQL

Question

I want to count the number of aircon and client under the same usr_id. Here's my resources:

clients_db

+---------+--------+ | clnt_id | usr_id | +---------+--------+ | 1 | a1 | +---------+--------+ | 2 | a1 | +---------+--------+ | 3 | a2 | +---------+--------+ | 4 | a1 | +---------+--------+

aircon_client_db

+---------+--------+---------+ | ac_id | usr_id | clnt_id | +---------+--------+---------+ | 1 | a1 | 1 | +---------+--------+---------+ | 2 | a2 | 2 | +---------+--------+---------+ | 3 | a2 | 1 | +---------+--------+---------+ | 4 | a2 | 3 | +---------+--------+---------+

According to the tables above. I want to count

how many clnt_id under the same usr_id
how many ac_id under the same usr_id

So I coded:

select count(acdb.ac_id) as nAC, count(clnt.clnt_id) as nClnt from aircon_client_db acdb left join clients_db clnt on clnt.usr_sid=acdb.usr_sid where acdb.usr_sid='a1'

I expect the answer as following:

3
1

But as I tested. My results are the same for both count - 4. Where did I wrong?

Your tables do not appear to be normalized. Please explain what each table is supposed to be doing here. — Tim Biegeleisen
– Tim Biegeleisen, Commented Jun 3, 2019 at 8:16
@TimBiegeleisen usr_id=mechanic logged in, client_db=mechanic's clients, aircon_client_db=client's aircon detail — Wilf
– Wilf, Commented Jun 3, 2019 at 8:21
'Where did I wrong?' - If you are analysing by user_id I would expect to see user_id in the select and a group by user_id. But I think the you conception is flawed and the query should be driven by whatever table holds the user details. — P.Salmon
– P.Salmon, Commented Jun 3, 2019 at 8:29

forpas · Accepted Answer · 2019-06-03 08:29:34Z

You want to count:
clnt_ids from the table clients_db and
ac_ids from the table aircon_client_db
for usr_sid='a1', right?
I don't see the necessity of joining the tables.
You can count separately with 2 subqueries in the same query:

select (select count(ac_id) from aircon_client_db where usr_sid = 'a1') as nAC, (select count(clnt_id) from clients_db where usr_sid = 'a1') as nClnt

If there is a case of duplicate clnt_ids in clients_db or duplicate ac_ids in aircon_client_db, then use:
count(distinct clnt_id) and count(distinct ac_id)

Collectives™ on Stack Overflow

Count separate row on same id from different table : MySQL

1 Answer 1

Comments

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Related