dev-ria Posted October 15, 2012 Share Posted October 15, 2012 hello, so i have a db with a list of domains. i need to count all the domains, then display only top that have the most count, and subtract those from the total so i can have an "other" field...so it would be total domains - 70 domainx.com - 10 domainxy.com - 20 domainxyz.com - 30 other - 10 i have already $query = mysql_query(" SELECT domain, COUNT(domain) AS domaincount FROM domain_table GROUP BY domain ORDER BY COUNT(domain) DESC LIMIT 10"); but its not enough to determine what the "other" value will be Link to comment https://forums.phpfreaks.com/topic/269489-count-all-results-and-subtract-them/ Share on other sites More sharing options...
Psycho Posted October 15, 2012 Share Posted October 15, 2012 I think you could do this one of two ways. Which way I would choose would be based upon the total number of records. If you don't have a "lot" of records (i.e. hundreds) I would use one query to return the count for all domains and then process all of them: displaying the count for the top n records and then using the remaining records to get the other count. However, if you have a lot of records it may be more efficient to do two queries: one to get the top domains by count with a LIMIT clause and a second query to get the total count. So, option one would be to remove the LIMIT cause on your current query and option two would be to run a second query to get the total count (then subtract the counts of the top records). NOTE: Don't use "COUNT(domain)" in the ORDER BY clause - you already did that to get the dynamic value of "domaincount" so no need to do that calculation twice. Link to comment https://forums.phpfreaks.com/topic/269489-count-all-results-and-subtract-them/#findComment-1385322 Share on other sites More sharing options...
Psycho Posted October 15, 2012 Share Posted October 15, 2012 Here is some sample code for the two possible solutions above. I went ahead and incorporated the two queries for solution 2 into a single query statement with both the queries. I have not tested these so there may be some syntax errors, but the logic should be sound Option 1 $top_count = 10; //Query ALL domains and their counts $query = "SELECT domain, COUNT(domain) AS domaincount FROM domain_table GROUP BY domain ORDER BY domaincount DESC"; $result = mysql_query($query); $other_count = 0; while($row = mysql_fetch_assoc($result)) { if($top_count > 0) { //Display top domain and count echo "{$row['domain']}: {$row['domaincount']}<br>\n"; $top_count--; } else { //Calculate 'other' total $other_count += $row['domaincount']; } } //Display 'other' total echo "Other: {$row['domaincount']}<br>\n"; Option 2: $top_count = 10; //Query only the top 10 domains and run second //query with total count of ALL domains $query = "SELECT domain, COUNT(domain) AS domaincount, (SELECT COUNT(domain) FROM domain_table) AS totalcount FROM domain_table GROUP BY domain ORDER BY domaincount DESC LIMIT {$top_count}"; $result = mysql_query($query); while($row = mysql_fetch_assoc($result)) { if(!isset($other_count)) { $other_count = $row['totalcount'] } //Display top domain and count echo "{$row['domain']}: {$row['domaincount']}<br>\n"; //Reduce other_count by this domains count $other_count -= $row['domaincount']; } //Display 'other' total echo "Other: {$row['domaincount']}<br>\n"; Link to comment https://forums.phpfreaks.com/topic/269489-count-all-results-and-subtract-them/#findComment-1385325 Share on other sites More sharing options...
Barand Posted October 15, 2012 Share Posted October 15, 2012 Minor mod required Change //Display 'other' total echo "Other: {$row['domaincount']}<br>\n"; to //Display 'other' total echo "Other: $other_count<br>\n"; Link to comment https://forums.phpfreaks.com/topic/269489-count-all-results-and-subtract-them/#findComment-1385340 Share on other sites More sharing options...
dev-ria Posted October 16, 2012 Author Share Posted October 16, 2012 wow!! this worked perfectly!!! I really appreciate it! Link to comment https://forums.phpfreaks.com/topic/269489-count-all-results-and-subtract-them/#findComment-1385506 Share on other sites More sharing options...
Recommended Posts
Archived
This topic is now archived and is closed to further replies.