I'm a bit stumped on how to achieve something in SQL - might be missing something obvious. I am wanting to calculate the % a value in a column contributes to the total of this column, but that matches to several other column criteria

I have some data that looks something like this:

I would like to calculate a % for each row that is the percentage that the country's total is of the total - where Date, Cust, Sector and Type match.

So for example, row 1, US % would be 0.0725 ( 312 / (312 3987) ), and row 2 would be 0.9275 , as date, cust, sector and type match, but wouldn't include 122 (row 3) in the calculation as sector and type doesn't match.

I can calculate it using the sum of total for all of the data returned (below code, simplified) - but not sure how to break it down to the totals matching records of several columns only

SELECT COUNTRY, DATE, CUST, SECTOR, TYPE, SUM(TOTAL), SUM(TOTAL) / (SELECT SUM(TOTAL) FROM Data) as PERCENT
FROM Data
GROUP BY COUNTRY, DATE, CUST, SECTOR, TYPE;

Hope that makes sense - any help appreciated

CodePudding user response：

So you can use a WINDOW FRAME version of sum like so:

with data(Country, Date, Cust, Sector, Type, Total) as (
    select * from values 
        ('US', '201902', 'ABCD', 'ABC-DEF', 'OTHER', 312),
        ('DE', '201902', 'ABCD', 'ABC-DEF', 'OTHER', 3987),
        ('US', '201902', 'ABCD', 'DEF-GHI', 'MAIN', 122)
)
select d.*
    ,round(100 * total / sum(total) over(partition by d.date, d.cust, d.sector, d.type),2) as percent
from data as d

gives:

but if you have many values you want to roll-up,

with data(Country, Date, Cust, Sector, Type, Total) as (
select * from values 
    ('US', '201902', 'ABCD', 'ABC-DEF', 'OTHER', 212),
    ('US', '201902', 'ABCD', 'ABC-DEF', 'OTHER', 100),
    ('DE', '201902', 'ABCD', 'ABC-DEF', 'OTHER', 3987),
    ('US', '201902', 'ABCD', 'DEF-GHI', 'MAIN', 122)
)

select sum(Total) Total , Country ,type, Cust, Sector, Date from data group by 
grouping sets (Country ,type, Cust, Sector, Date) ;