Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 5 of 5
  1. #1
    New Coder
    Join Date
    Nov 2011
    Posts
    23
    Thanks
    0
    Thanked 0 Times in 0 Posts

    COUNT() twice in one query showing weird results

    Ok this is my query:

    Code:
    SELECT COUNT(d.dragon_id) as dragon_count, COUNT(pm.id) as pm_count
    FROM user_creature d
    LEFT JOIN private_message pm ON pm.to_user = d.user_id
    WHERE d.user_id = 4
    LIMIT 1
    It's giving me 10 as dragon_count and 10 as pm_count when in reality, it should be 1 as dragon_count and 10 for pm_count.
    Why does dragon_count go up to 10 when I add on the private_message table and do the COUNT() for that. Without the private_message table included and in the SELECT, it pulls 1 just fine..

  • #2
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    26,014
    Thanks
    79
    Thanked 4,436 Times in 4,401 Posts
    Nope, you are wrong.

    Let's demonstrate why:
    Code:
    SELECT d.dragon_id, pm.id AS message_id
    FROM user_creature d
    LEFT JOIN private_message pm ON pm.to_user = d.user_id
    WHERE d.user_id = 4
    Don't you get 10 rows from that?

    So aren't there 10 rows with a dragon_id? Yes, they are all the same id. But that's irrelevant, to SQL.

    So of course when you then do COUNT(dragon_id) you get 10.

    *TRIVIAL* fix:
    Code:
    SELECT COUNT(DISTINCT d.dragon_id) as dragon_count, COUNT(pm.id) as pm_count
    FROM user_creature d
    LEFT JOIN private_message pm ON pm.to_user = d.user_id
    WHERE d.user_id = 4
    LIMIT 1
    Hmm??
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.

  • #3
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    26,014
    Thanks
    79
    Thanked 4,436 Times in 4,401 Posts
    By the way, your LIMIT 1 is a waste of code. That query will never return more than one row, as it is written.
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.

  • #4
    New Coder
    Join Date
    Nov 2011
    Posts
    23
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Wow.. you're right. I'm so confused.

    The LIMIT 1 is a faster query. A lot of developers have notified me that I should be including LIMIT 1 in my queries.. Now I shouldn't?

  • #5
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    26,014
    Thanks
    79
    Thanked 4,436 Times in 4,401 Posts
    Try it both with and without the LIMIT 1 and see if your results are any different.

    If they are--with that query written as is--I owe you a latte.

    You only use LIMIT when your query is going to return more records than you want.

    And in any case, a LIMIT used *WITHOUT* and ORDER BY clause is worse than useless.
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.


  •  

    Posting Permissions

    • You may not post new threads
    • You may not post replies
    • You may not post attachments
    • You may not edit your posts
    •