按查询获取组中每个组的和

I am using MYSQL and I have following data in database...

我正在使用MYSQL，我在数据库中有以下数据…

It is a group by query as

它是一个由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 |
+------+---------+------------+-------------+

What should be the query if I want to take the data as shown below:

如果我想获取如下所示的数据，查询应该是什么:

+------+-----------+--------+-----------+------------+-------------+
| 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 |
+------+-----------+--------+-----------+-----------+--------------|

Actually I want to get sum for each group...

实际上我想要得到每组的和。

2 个解决方案

#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

And reorder the fields as you need

并根据需要重新排序字段。

#2

In oracle/SQL server the following will work:

在oracle/SQL server中，以下将工作:

select year, 
sum(sum(profit)) over (partition by year) as YearProfit,
country, 
sum(sum(profit)) over (partition by year, country) as CountryProfit,
product, 
sum(sum(profit)) over (partition by year, country, product) as ProductProfit
from [table]
group by year, country, product

#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