Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 2 of 2
Thread: Remove Duplicates From Query
12-27-2011, 04:23 PM #1
- Join Date
- Aug 2011
- Thanked 0 Times in 0 Posts
Remove Duplicates From Query
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.
$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';
12-27-2011, 08:25 PM #2
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.
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.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';
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.