How to use GROUP BY to count a new category and old categories at once

Question

For example, I have data like this:

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

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

Which Group C means Group A&B

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

Answer 1

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

DB2, HANA, Oracle, PostgreSQL 9.5, SQL Server, Sybase SQL Anywhere

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

Others

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