Adding a new row in data using the existing rows and based on a column using SQL-CodePudding

I have the data in the attached format in R. I am making use of sqldf to solve the following challenge :

I need to add the yellow cells to my existing data(the light orange cells). The Total amount should be added as a row in the existing data considering Type 1,2,3,4 based on the color. All in column Color refers to both categories (B & W).All in column Product refers to Product Type 1, 2, 3 & 4. I have used the following code

select country, state, district, color, product, amount from Table
union all
select distinct country, state, district, color, "All", sum(amount) from Table
group by country, state, district, color

I end up getting either 0 in the amount or end up getting multiple Alls with 0 corresponding to the rows.

CodePudding user response：

Try applying a partial aggregation with window functions distinct keyword in the second select statement:

select country, state, district, color, product, amount from Table
union all
select distinct country, state, district, color, "All",
                sum(amount) over(partition by country, state, district, color)
from Table

This should work on all the most common DBMS', which sqldf is currently interfacing with.