Count All Results And Subtract Them

[dev-ria]

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

[Psycho]

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.

Edited October 15, 2012 by Psycho

[Psycho]

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";

Edited October 15, 2012 by Psycho

[Barand]

Minor mod required

 

Change

//Display 'other' total
echo "Other: {$row['domaincount']}<br>\n";

 

to

//Display 'other' total
echo "Other: $other_count<br>\n";

[dev-ria]

wow!! this worked perfectly!!! I really appreciate it!