How to sum few colums values for each type and cal…

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.

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

Advertisement

Answer