mysql: COUNT returns wrong number

Question

everyone. I have 2 tables

Lobby:

|lobby_id (pk, a_increment) | t_id | u_id |
-------------------------------------------
|1                          | 6    | 131  |

Trips:

|tid | t_name | t_desc | max_size |
----------------------------------
|6   | GO TO HELL | DSADA | 13  |
|7   | GO TO das  | DSADA | 9   |
|7   | GO TO das  | DSADA | 9   |

I need to count u_id from lobby where t_id = 6 (tid in Trips table) and display: count and max_size from (trips) my sql query so simple:

SELECT count(b.u_id) as counter, a.size as p_size
        from trips a
        left join lobby b ON b.t_id=6

But the query result show me not counter = 1 and max_size = 13, this query returs counter 3 and max_size 13

Giorgos Betsos · Accepted Answer · 2016-04-14 20:55:07Z

2

You need a predicate matching records from both tables in the ON clause of your query:

SELECT count(b.u_id) as counter, b.max_size 
from trips a
left join lobby b ON a.tid = b.tid 
WHERE a.t_id=6

answered Apr 14, 2016 at 20:55

Giorgos Betsos

72.3k10 gold badges69 silver badges103 bronze badges

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

2 Comments

ATIKON Over a year ago

Hi, thank you so much. You save a lot of my time, cause i'm not good in sql :(

Giorgos Betsos Over a year ago

@ATIKON Glad I was able to help. Please mark this, or any other, answer as accepted if it helped you solve your problem.

Gordon Linoff · Accepted Answer · 2016-04-14 21:45:00Z

0

I don't understand why you have a join at all:

SELECT COUNT(*) as counter, MAX(a.size) as p_size
FROM trips t
WHERE t.tid = 6

All the information is in one table.

answered Apr 14, 2016 at 21:45

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

1 Comment

ATIKON Over a year ago

I need to count u_id from lobby.

Collectives™ on Stack Overflow

mysql: COUNT returns wrong number

Lobby:

Trips:

2 Answers 2

2 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

Lobby:

Trips:

2 Answers 2

2 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related