Mysql

How to group upper and lower limits

[trebian]

Hi forum. Not sure if MySQL related question can be posted here, if not my apology.

ISSUE:
During grouping (count of a category for instance) I would like to get the top 15 counts, and than everything below a threshold lump into an OTHER category.

EXAMPLE:

select account, count(account) as total
 from abc
  group by account
   having count(account)>10;

EXPLANATION:
In the above code, I would like to get the accounts having count of gt 10, and the remaining that were less than 10 count be lumped into a OTHER category (with the total count)

[brucec]

That's obviously a very good start.

One way of accomplishing this, I would make this a IF conditional in PHP and use your "total" alias as the test for the IF statement. My method can eliminate the Having clause as well.

I can see it working something like this:

$query=mysql_query("select account, count(account) as total from abc GROUP BY account") or die ("whatever");

while($CountRS=mysql_fetch_array($query)) {
$total = $CountRS["total"];
$account = $CountRS["account"];

if ($total>10) {
echo "$account: $total<br>";
} else {
$runningtotal += $total;
}
}

echo "Other: $runningtotal";

I can see it working like that.

[brucec]

Oh I did not see that you wanted only the top 15 counts.

[brucec]

You could also throw an ORDER BY DESC clause in there as well.

Your IF conditional would have be included inside another WHILE loop that would BREAK after a counter hits 15.