Mysql returning incorrect COUNT when using more than one JOIN?

Question

I have 3 tables: NAMES, REGISTRATIONS, and RENEWALS. I'm using LEFT JOIN to join the 3 tables with a common ID.

I need to count the number of REGISTRATIONS of each user, as well as the number of RENEWALS. I've tried using different options in the GROUP BY field, but none seemed to work.

Here's the SELECT statement:

SELECT 
    names.name_id AS 'Names ID'
    ,names.name AS Name
    ,count(registrations.date) AS Registrations
    ,count(renewals.date) AS Renewals

FROM names

LEFT JOIN registrations

    ON names.name_id = registrations.name_id

LEFT JOIN renewals

    ON renewals.name_id = registrations.name_id

GROUP BY names.name_id, registrations.name_id, renewals.name_id;

And here are the 3 tables:

    TABLE: names
    +---------+------+
    | name_id | name |
    +---------+------+
    |       1 | Ana  |
    |       2 | John |
    |       3 | Paul |
    +---------+------+

    TABLE: registrations
    +-----------------+---------+---------------------+-------+
    | registration_id | name_id | date                | value |
    +-----------------+---------+---------------------+-------+
    |               1 |       1 | 2014-01-30 13:15:02 | 15    |
    |               2 |       2 | 2014-05-01 18:01:44 | 15    |
    |               3 |       2 | 2014-07-08 15:10:43 | 20    |
    |               4 |       3 | 2012-09-28 17:45:32 | 15    |
    |               5 |       3 | 2014-01-09 18:26:14 | 20    |
    |               6 |       3 | 2015-01-10 13:22:01 | 25    |
    +-----------------+---------+---------------------+-------+

    TABLE: renewals
    +------------+---------+---------------------+-------+
    | renewal_id | name_id | date                | value |
    +------------+---------+---------------------+-------+
    |          1 |       1 | 2015-01-30 00:00:00 | 5     |
    |          2 |       1 | 2016-02-12 00:00:00 | 5     |
    |          3 |       1 | 2015-06-01 00:00:00 | 5     |
    |          4 |       1 | 2013-11-24 00:00:00 | 5     |
    |          5 |       2 | 2015-01-27 00:00:00 | 5     |
    +------------+---------+---------------------+-------+

Here's the INCORRECT result I'm getting:

    +----------+------+---------------+----------+
    | Names ID | Name | Registrations | Renewals |
    +----------+------+---------------+----------+
    |        1 | Ana  |             4 |        4 |
    |        2 | John |             2 |        2 |
    |        3 | Paul |             3 |        0 |
    +----------+------+---------------+----------+

The CORRECT result I was expecting would be:

    +----------+------+---------------+----------+
    | Names ID | Name | Registrations | Renewals |
    +----------+------+---------------+----------+
    |        1 | Ana  |             1 |        4 |
    |        2 | John |             2 |        1 |
    |        3 | Paul |             3 |        0 |
    +----------+------+---------------+----------+

How can I fix the query to get a correct result?

Using count(distinct ...) might work for you. That would assume that your dates are "unique" per registration/renewal etc. Since you have times resolved to the second it's probably fine. In other problems it might no work so cleanly. The generic fix is to generate the counts for each table individually and then join those results. — shawnt00
– shawnt00, Commented Nov 4, 2016 at 17:22

CLAbeel · Accepted Answer · 2016-11-04 17:25:29Z

2

Try this:

SELECT 
    names.name_id AS 'Names ID'
    ,names.name AS Name
    ,count(distinct registrations.registration_id) AS Registrations
    ,count(distinct renewals.renewal_id) AS Renewals
FROM names    
LEFT JOIN registrations    
    ON names.name_id = registrations.name_id    
LEFT JOIN renewals    
    ON renewals.name_id = registrations.name_id    
GROUP BY names.name_id, registrations.name_id, renewals.name_id;

Whenever I run into this type of issue, I find it helps to just run a select * query if your server can take it. Like this:

SELECT *
FROM names    
LEFT JOIN registrations    
    ON names.name_id = registrations.name_id    
LEFT JOIN renewals    
    ON renewals.name_id = registrations.name_id    ;

That will let you see what you are really counting.

answered Nov 4, 2016 at 17:25

CLAbeel

1,08814 silver badges21 bronze badges

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

Comments

Erik · Accepted Answer · 2016-11-04 17:22:58Z

0

Your query is executed just fine.

After the first join you have 1 entry for Ana, 2 entries for John and 3 for Paul.

After the seconds join the one entry for Ana is duplicated 4 times and joined (concatenated) with the 4 renewals. If you now count the registration dates for Ana you get 4. That is where your "errors" come from.

You could for example count the distinct dates to fix it.

answered Nov 4, 2016 at 17:22

Erik

3,0982 gold badges22 silver badges35 bronze badges

1 Comment

noahstime Over a year ago

Adding DISTINCT did the job. Thank you.

Collectives™ on Stack Overflow

Mysql returning incorrect COUNT when using more than one JOIN?

2 Answers 2

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related