Group by 'other', where groups with small contribution are included in 'other' field - MySQL

Question

I would like to GROUP BY an item in my table, which has an associated monetary value. This I can work out as a percentage, however there are often too many items in the list (too many to be useful, for example in a graph). Consequently, I would like to put those items that have a contribution to the total of less than say 5% into an 'Other' group. Any thoughts?

thanks (using MySQL > v5)

Answer 1

You asked for thoughts. I could write the SQL for you, but I wonder (based on your overall design) you might not find it easier to do a little more work in the BL or UI, with an array for instance, to accomplish the same thing. For one thing, the SQL will necessarily be more complicated and less efficient than you might like.

Answer 2

create a temp table and have two insert statements, one of your primary stuff and one of the other group, then just select all from the temp table.

Answer 3

Looks like a UNION could help, e.g. (depending how your table's organized, of course):

SELECT itemname, SUM(percent) AS pc FROM items
GROUP BY itemtype
HAVING SUM(percent) >= 0.05
UNION
SELECT 'Other', SUM(*) FROM
  (SELECT SUM(percent) FROM items
   GROUP BY itemtype
   HAVING SUM(percent) < 0.05)

If you don't have a percent field but only (say) a value field (so the % needs to be dynamically computed as 100 times the fraction of an item's value to the grand total) you can get the grand total from yet another nested SELECT, but at some point it may be worth switching to procedural approaches as other respondents hint.