CODEWITHSUNDEEP
sqlsnowflake-cloud-data-platform
Alex S
Alex S

Reputation: 13

Calculate % based on matching column values

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:

Country Date Cust Sector Type Total %
US 201902 ABCD ABC-DEF OTHER 312 ?
DE 201902 ABCD ABC-DEF OTHER 3987 ?
US 201902 ABCD DEF-GHI MAIN 122 ?

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

Upvotes: 1

Views: 186

Answers (2)

Adrian White
Adrian White

Reputation: 1804

Grouping_Sets() is also quite powerful - essentially does any->all combos without tonnes of code.

enter image description here

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) ;

Upvotes: 0

Simeon Pilgrim
Simeon Pilgrim

Reputation: 25968

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:

COUNTRY DATE CUST SECTOR TYPE TOTAL PERCENT
US 201902 ABCD ABC-DEF OTHER 312 7.26
DE 201902 ABCD ABC-DEF OTHER 3,987 92.74
US 201902 ABCD DEF-GHI MAIN 122 100

but if you have many values you want to roll-up, RATIO_TO_REPORT can help do some of the lifting:

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 d.country, d.Date, d.Cust, d.Sector, d.Type
    ,sum(total) as part
    ,ratio_to_report(part) over( partition by d.date, d.cust, d.sector, d.type) 
from data as d
group by 1,2,3,4,5
COUNTRY DATE CUST SECTOR TYPE PART PERCENT
US 201902 ABCD ABC-DEF OTHER 312 0.072575
DE 201902 ABCD ABC-DEF OTHER 3,987 0.927425
US 201902 ABCD DEF-GHI MAIN 122 1

Upvotes: 1

Related Questions