I have below query which gives me data with three columns - type, amount and total for current week using week_number column. Below is the output I get back as of now with above query: Problem Statement Now I need to calculate the percentage for each type by using below formula: For each type (sum(total) - sum(amount)) / sum(amount). So

How to sum few colums values for each type and calculate percentage?

I have below query which gives me data with three columns – type, amount and total for current week using week_number column.

select type,
case
WHEN (type = 'PROC1' AND contractdomicilecode = 'UIT') THEN 450
WHEN (type = 'PROC1' AND contractdomicilecode = 'KJH') THEN 900
WHEN (type = 'PROC2' AND contractdomicilecode = 'LOP') THEN 8840
WHEN (type = 'PROC2' AND contractdomicilecode = 'AWE') THEN 1490
WHEN (type = 'PROC3' AND contractdomicilecode = 'MNH') THEN 1600
WHEN (type = 'PROC3' AND contractdomicilecode = 'LKP') THEN 1900
END as amount,
total
from xyz.orders pa
join
(select clientid as clientid, max(version) as version
from xyz.orders where consumerid IN (select distinct entity_id from abc.items
where week_number = extract(week from current_date)
and item_type like '%Ionize - Data%' )
and createdfor ='BLOCK'
and holder='RELAY_FUTURES'
group by clientid) pb on
pa.clientid = pb.clientid and pa.version = pb.version;

Below is the output I get back as of now with above query:

type    amount      total
---------------------------
PROC1    450         1768
PROC1    900         123
PROC1    450         456
PROC2    8840        99897
PROC2    1490        2223
PROC2    8840        9876
PROC3    1900        23456
PROC3    1600        12498
PROC3    1600        28756

Problem Statement

Now I need to calculate the percentage for each type by using below formula:

For each type (sum(total) – sum(amount)) / sum(amount).
So for PROC1 type, (sum of all ‘total’ coumn – sum of all ‘amount’ column) / sum of all ‘amount’ column.
Similarly for PROC2 and PROC3 as well.
Also I need extra OVERALL type as well which will be sum of all PROC1 + PROC2 + PROC3 values.

At the end I should be seeing this output:

type    sum_amount   sum_total   percentage
----------------------------------------------
PROC1    1800         2347          0.3038
PROC2    10330      111996          9.841
PROC3    5100        64710          11.688
OVERALL  17230      179053          9.3919

Is this possible to do in redshift?

Answer

You can phrase this as:

with t as (
      <your query here>
     )
select type, sum(amount) as amount, sum(total) as total,
       (sum(total) - sum(amount)) * 1.0 / sum(amount) as ratio
from t
group by type, amount, total
union all
select 'Overall', sum(amount), sum(total),
       (sum(total) - sum(amount)) * 1.0 / sum(amount) as ratio
from t;

In practice, this will put the “Overall” column last. However, that is not guaranteed unless you add an ORDER BY.

Advertisement

Answer