如何使用GROUP BY一次计算新类别和旧类别 [英] How to use GROUP BY to count a new category and old categories at once

问题描述

例如，我有这样的数据:

For example, I have data like this:

   Group    Money
    A       100
    A       200
    B       100
    B       300
    B       110

我想使用GROUP BY(通过其他方式)来汇总我的数据，如下所示:

I want to use GROUP BY(ore something else) to summarize my data like this:

   Group    Money  Average  Count
    A       300     150       2
    B       510     170       3
    C       810     162       5

C组是指A& B组

Which Group C means Group A&B

有什么方法可以通过简单的方式获得结果吗?

Is there any way to get the outcome in some simple way?

推荐答案

您要查找的是ROLLUP.可以通过不同的方式来完成此操作，具体取决于您所使用的数据库:

What you're looking for is a ROLLUP. This can be done in different ways, depending on the database you're using:

在SQL标准中也对此进行了指定:

This is also specified as such in the SQL standard:

SELECT COALESCE("Group", 'C'), SUM(Money), AVG(Money), COUNT(*)
FROM t
GROUP BY ROLLUP ("Group")

Cubrid，MariaDB，MySQL

SELECT COALESCE(`Group`, 'C'), SUM(Money), AVG(Money), COUNT(*)
FROM t
GROUP BY `Group` WITH ROLLUP

其他

SELECT "Group", SUM(Money), AVG(Money), COUNT(*)
FROM t
GROUP BY "Group"
UNION ALL
SELECT 'C', SUM(Money), AVG(Money), COUNT(*)
FROM t

这篇关于如何使用GROUP BY一次计算新类别和旧类别的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！