mysql - 通过查询获取组中每个组的总和

Question

我正在使用 MYSQL 并且我在数据库中有以下数据...

它是一个group by query as

select year, country , product,SUM(profit) 
from table 
group by year, country , product;

+------+---------+------------+-------------+
| year | country | product    | SUM(profit) |
+------+---------+------------+-------------+
| 2000 | Finland | Computer   |        1500 |
| 2000 | Finland | Phone      |         100 |
| 2000 | India   | Calculator |         150 |
| 2000 | India   | Computer   |        1200 |
| 2000 | USA     | Calculator |          75 |
| 2000 | USA     | Computer   |        1500 |
| 2001 | Finland | Phone      |          10 |
| 2001 | USA     | Calculator |          50 |
| 2001 | USA     | Computer   |        2700 |
| 2001 | USA     | TV         |         250 |
+------+---------+------------+-------------+

如果我想像下面这样取数据应该怎么查询:

+------+-----------+--------+-----------+------------+-------------+
| year |SUM(profit)|country |SUM(profit)| product    | SUM(profit) |
+------+-----------+--------+-----------+------------+-------------+
| 2000 | 4525      |Finland |1600       | Computer   |        1500 |
| 2000 | 4525      |Finland |1600       | Phone      |         100 |
| 2000 | 4525      |India   |1350       | Calculator |         150 |
| 2000 | 4525      |India   |1350       | Computer   |        1200 |
| 2000 | 4525      |USA     |1575       | Calculator |          75 |
| 2000 | 4525      |USA     |1575       | Computer   |        1500 |
| 2001 | 3010      |Finland |10         | Phone      |          10 |
| 2001 | 3010      |USA     |3000       | Calculator |          50 |
| 2001 | 3010      |USA     |3000       | Computer   |        2700 |
| 2001 | 3010      |USA     |3000       | TV         |         250 |
+------+-----------+--------+-----------+-----------+--------------|

其实我想得到每组的总和...

Answer 1

select 
    data.*,
    d2.country_sum,
    d3.year_sum
from 
    (select year, country , product,SUM(profit) 
     from table t1
     group by year, country , product) data 
      JOIN
        (select year, country,SUM(profit) as country_sum
         from table t1
         group by year, country ) d2 ON data.year=d2.year and data.country=d2.country
      JOIN
        (select year, SUM(profit) as year_sum
         from table t1
         group by year) d3 ON data.year=d2.year

并根据需要重新排序字段