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 2 of 2
  1. #1
    New Coder
    Join Date
    Aug 2011
    Posts
    52
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Remove Duplicates From Query

    Hey guys.

    I have a problem where i am trying to remove all of the rows with duplicate email address but its not working here is my query.

    PHP Code:
    $query_str4 'SELECT DISTINCT email, COUNT( afid_seller_name )'
                                    
    ' FROM lead_partners_pages'
                                    
    ' WHERE afid_seller_name = \'CIQFY\''
                                    
    ' AND MONTH(date_day) = MONTH(NOW())AND partner_id_buyer IS NOT NULL AND partner_id_buyer NOT IN (\' \',\'\')'
                                    
    ' GROUP BY afid_seller_name, date_day'
                                    
    ' ORDER BY afid_seller_name, date_day'

  • #2
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    25,825
    Thanks
    78
    Thanked 4,413 Times in 4,378 Posts
    You can *NOT* GROUP BY a field that you are doing a COUNT() of.

    And you should not GROUP BY a field that is not in the SELECT.

    And you should GROUP BY any field that *is* in the SELECT.

    And there is no reason to use DISTINCT if you are using GROUP BY on the same field.

    And if you use "..." in PHP then you don't have to escape the apostrophes in the MySQL query.

    And there's a sneaky way to consolidate your two partner_id_buyer tests.

    So:
    Code:
    $query_str4 = 'SELECT email, date_day, COUNT( afid_seller_name )'
                                    . ' FROM lead_partners_pages'
                                    . " WHERE afid_seller_name = 'CIQFY' "
                                    . ' AND MONTH(date_day) = MONTH(NOW()) '
                                    . " AND TRIM( IFNULL( partner_id_buyer, '' ) ) <> '' "
                                    . ' GROUP BY email, date_day'
                                    . ' ORDER BY email, date_day';
    If you don't really want the count by date_day, then remove it from the SELECT *and* from the GROUP BY and ORDER BY.
    Last edited by Old Pedant; 12-27-2011 at 08:29 PM.
    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
    •