按“其他”分组，其中贡献较小的组包含在“其他”字段中 - MySQL

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?

我想在我的表中使用GROUP BY一个项目,该项目具有相关的货币价值。这个我可以按百分比计算,但是列表中的项目通常太多(太多而无用,例如在图表中)。因此,我想将那些对总数低于5%的贡献的项目纳入“其他”组。有什么想法吗?

thanks (using MySQL > v5)

谢谢(使用MySQL> v5)

3 个解决方案

#1

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

看起来UNION可以提供帮助,例如(当然,取决于你桌子的组织方式):

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.

如果您没有百分比字段但只有(比方说)一个值字段(因此%需要动态计算为项目值与总计的百分比的100倍),您可以从另一个嵌套中获得总计选择,但在某些时候,可能值得转换到程序方法,因为其他受访者暗示。

#2

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.

你问了想法。我可以为你编写SQL,但我想(根据你的整体设计)你可能会发现在BL或UI中做一些工作更容易,例如使用数组来完成同样的事情。首先,SQL必然会比您想要的更复杂,效率更低。

#3

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.

创建一个临时表,并有两个插入语句,一个主要的东西和另一个组,然后只需从临时表中选择所有。

#1