CODEWITHSUNDEEP
mysqljoincount
bikey77
bikey77

Reputation: 6672

mysql join 2 tables and count all rows from each table

I have table contracts c (id, exp_date) and table members m (id, cid). I need to count all contracts and all members together joining the 2 tables on m.cid = c.id.

I tried this but obviously isn't not right as it returns a same count result from both tables

SELECT count(m.id) as totmembers , count(c.id) as totcontracts
from members m
join contracts c on m.cid = c.id
where DATEDIFF(c.im_exp, CURDATE()) > 0

Results should be something like 5000 contracts, 12.000 members but i'm getting 12.000 for both totmembers and totcontracts.

Upvotes: 0

Views: 335

Answers (2)

gUIDo
gUIDo

Reputation: 86

Try this:

SELECT count(m.id) as totmembers , count(distinct c.id) as totcontracts
from members m
join contracts c on m.cid = c.id
where DATEDIFF(c.im_exp, CURDATE()) > 0

Upvotes: 1

Peter Kiss
Peter Kiss

Reputation: 9319

Becouse you are created an INNER JOIN statement. Create a new query with two individual query int the SELECT list.

SELECT (SELECT ... WHERE ...) AS totmembers, (SELECT ... WHERE ...) AS totcontracts

Upvotes: 0

Related Questions