...

View Full Version : Remove Duplicates From Query



Atrhick
12-27-2011, 04:23 PM
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.



$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';

Old Pedant
12-27-2011, 08:25 PM
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:


$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.



EZ Archive Ads Plugin for vBulletin Copyright 2006 Computer Help Forum