Postgres - How to make a case for when it gets to certain account, it splits revenue between sales regions

Say I have the following tables:

Account | Revenue | Sales Region
--------|---------|--------------
Toyota  | 718935  | US - East
--------|---------|--------------
Safeway | 327895  | US - West
--------|---------|--------------
MLB     | 1028943 | US - East

&

Sales Region | Total Revenue
-------------|---------------
US - East    | 1747878
-------------|---------------
US - West    | 327895 

How would I go about splitting the revenue between both sales regions when it gets to the MLB account? So instead of "US - East" getting the full $1028943, both sales regions would get $514471.50

1 answer

  • answered 2018-03-13 22:37 Jorge Campos

    One way to solve it since your only criteria is to hard code the name for the split would be:

    select a.region, 
           sum(a.revenue) + ( (select sum(revenue) 
                                 from accounts 
                                where name in ('MLB')
                              )/
                              (select count(*) 
                                 from accounts 
                                where name not in ('MLB')
                              )
                            )
      from accounts a
     where a.name not in ('MLB')
     group by a.region
    

    Notice that I'm using IN/NOT IN on those subqueries in case you decide to split more than one account.

    See it working here: http://sqlfiddle.com/#!17/7ef3a/13