I have a table in SQL that looks like this

Person  brand   brand_spend category    category_spend
0   p1  b1       20           c1           100
1   p1  b2       50           c1           100
2   p2  b1       25           c2           40
3   p1  b3       30           c1           100
4   p1  b2       15           c2           70

I need to tag every customer based on percentage spend he has made at a brand based on total category spend where that brand is present. So essentially I would want to tag Person p1 for brand b1' as percentage spend at 'b1 which should be calculated as 45/ 140

How this can be achieved. If I roll up on brand level to find total category spend then I think duplicates rows would add up.

I just want to find customer's Spend at a brand based on total spend at Brand for all categories where that brand is present.

2 Answers

cars10m On

You need grouping on two levels, as shown here:

select person, brand, sum(brand_spend) personspend, spendbrandallcats, 
       round(sum(brand_spend)/spendbrandallcats,3) pbratio from tbl t 
inner join (  -- brand statistics: sum of all spends per brand in all categories
 select brand br, sum(casp) spendbrandallcats from tbl 
 inner join ( -- category statistics: total category sums
   select category ca, sum(brand_spend) casp from tbl group by category
 ) catspend ON ca=category
 group by brand
) brandstats on br=brand
group by person,brand

These are the results:

person brand personspend spendbrandallcats pbratio
p1     b1    20          140               0.143
p1     b2    65          140               0.464
p1     b3    30          100               0.3
p2     b1    25          140               0.179

See the little demo here: https://rextester.com/SVNH27609

Seema Mudgil On

Sometimes things are not as complicated as it seems and a simple query does the trick.

select person , brand , sum(prsn_brand_spend) ,
sum(category_spend)  , (sum(prsn_brand_spend)/sum(category_spend)) as perc_spend
from tbl group by person , brand 

The Results are

person  brand   brand_spend category_spend  perc
    p1  b1       20            100           0.200000
    p1  b2       65            170          0.382353
    p1  b3       30            100          0.300000
    p2  b1       25            40          0.625000