Query Help Needed with using COUNT and JOIN

Question

I am looking for help in writing a MYSQL query. I have two tables, one called users and another called switched. The users table has the following structure:

uid
first_name
last_name

And the switched table:

uid (references user table)
switch_number
reg_date

The switched table has multiple uid values and I wish to generate a table with the following output:

uid | switches

Where switches refers to the number of occurrences in the switched table. Could someone please guide me on how to do this using MySQL and PHP. I appreciate any help.

Regards,

does your user table have anything to do with the desired output? — Jay Kazama
– Jay Kazama, Commented Jul 17, 2014 at 14:18
Providing sample data and desired output would help better illustrate the problem and desired solution. At this time I can't see why you would need the first table at all. Select UID, count(Switches) from switched group by UID seems like what your after but I can't tell. counting switches means NULL switch_numbers will be omitted if that situation exists. — xQbert
– xQbert, Commented Jul 17, 2014 at 14:18

juergen d · Accepted Answer · 2014-07-17 14:18:13Z

6

This will output the uids found in the switched table and their counts

select uid, count(switch_number) as switches
from switched
group by uid

If you need zero counts too then do

select u.uid, count(s.switch_number) as switches
from users u
left join switched s on s.uid = u.uid
group by u.uid

answered Jul 17, 2014 at 14:18

juergen d

205k40 gold badges304 silver badges377 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

deru · Accepted Answer · 2014-07-17 14:21:07Z

0

try something like that:

select uid, Count(*) from switched Group by uid

answered Jul 17, 2014 at 14:21

deru

4701 gold badge5 silver badges16 bronze badges

Comments

Orzech · Accepted Answer · 2014-07-17 14:18:44Z

-1

If I understood correctly, you want a GROUP BY clause in your statement.

answered Jul 17, 2014 at 14:18

Orzech

91 silver badge3 bronze badges

2 Comments

juergen d Over a year ago

This is not more than a comment.

Orzech Over a year ago

Right, sorry for that

Collectives™ on Stack Overflow

Query Help Needed with using COUNT and JOIN

3 Answers 3

Comments

Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related