CODEWITHSUNDEEP
postgresql
Piechartking
Piechartking

Reputation: 135

Postgres Column Values Apportionment

I run revenue queries for my team, but we apportion revenue with another team. However, when I'm running the query in Postgress, I don't know how to apportion the value of a column so that I don't get 100% of the revenue that I'm supposed to be splitting with another 3 other teams (I should only count 25% of the revenue that comes in). Below is my query:

select to_char("Date", 'Mon/YYYY') as "Date",
 sum("Amount") FILTER (WHERE 
 ("Type" = 'C021') or --Shared with 3 other teams, only count 25%
 ("Type" = 'C031') or --Shared with 3 other teams, only count 25%
 ("Type" = 'C041') or --Shared with 3 other teams, only count 25%
)
as "Revenue",
from "Transactions"
where "Date" between '01/01/2015' and '12/31/2015'
group by 1
order by min("Date");

As you can see, I get the data from the table "Transactions". The revenue comes from 3 customers, C021, C031, and C041, and is added together to make the "Revenue" column.

However, I'd like to only count 25% of each customer so that the values added together only represent 25% of the revenue from each customer.

Upvotes: 0

Views: 59

Answers (1)

Mike Sherrill 'Cat Recall'
Mike Sherrill 'Cat Recall'

Reputation: 95642

Assuming that there are other type codes that you need 100% of the revenue for, you want a union rather than a filter.

select to_char("Date", 'Mon/YYYY') as "Date", .25 * sum("Amount") as sub_total
from "Transactions"
where "Type" in ('C021', 'C031', 'C041')
group by "Date"
union 
-- 100% of revenue for all other type codes. Adjust for your
-- actual situation.
select to_char("Date", 'Mon/YYYY') as "Date", sum("Amount")
from "Transactions"
where "Type" not in ('C021', 'C031', 'C041')
group by "Date"

You might need to adjust the second WHERE clause.

If you want only the total, this will return a single row per month. The expression to_char("Date", 'YYYY-mm') is more common; it sorts correctly as a string.

select "Date", sum(sub_total) as total
from (select to_char("Date", 'YYYY-mm') as "Date", .25 * sum("Amount") as sub_total
      from "Transactions"
      where "Type" in ('C021', 'C031', 'C041')
      group by "Date"
      union 
      select to_char("Date", 'YYYY-mm') as "Date", sum("Amount")
      from "Transactions"
      where "Type" not in ('C021', 'C031', 'C041')
      group by "Date" ) as subtotals
group by "Date"
order by "Date"

Upvotes: 1

Related Questions