Have Geek Brain

Group on "others" where groups with little input are included in "other" field - MySQL

I would like a GROUP BY element in my table that has an associated monetary value. This can be done as a percentage, but there are often too many items in the list (too many to be useful, for example, in a graph). Hence, I would like to put these items that contribute less than 5% total in the "Others" group. Any thoughts?

thanks (using MySQL> v5)

+1


source to share


3 answers


It looks like UNION can help, for example. (depending on how your table is organized):

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 percentage field, but only (say) a value field (so% needs to be dynamically calculated as 100 times the fraction of the item value to the total), you can get the total from another nested SELECT, but at some point it might be advisable to move towards procedural approaches, as other respondents hinted.

+1


source


You asked for thoughts. I could write SQL for you, but I'm wondering (based on your overall design) that you might not find it easier to do a little more work in BL or UI, with an array, for example, to do the same thing. First, SQL is bound to be more complex and less efficient than you might like.



0


source


create a temp table and create two insert statements, one of your main items and one of the other groups, then just select everything from the temp table.

0


source






More articles:


All Articles