I have a table created in Materialized view from 10 different tables. Part of it looks like this group_name value1 value2 group1 100 20 group2 200 40 unknown 300 60 TOTAL 600 120 I have to ...

Subtract from rows with certain values

I have a table created in Materialized view from 10 different tables.

Part of it looks like this

group_name	value1	value2
group1	100	20
group2	200	40
unknown	300	60
TOTAL	600	120

I have to rearrange all values from rows with value group_name = ‘unknown’ to other rows. The final table should look like this

group_name	value1	value2
group1	200	40
group2	400	80
TOTAL	600	120

So formula for ‘group1’ would be:

 unknown x group1 x (TOTAL-unknown) + group1

The table is created with massive code and please note – I didn’t write it, it was given to me and I have to work with it. I don’t like how it looks, so please spare your anger. Anyway, the query looks like this:

TABLESPACE pg_default
AS
 WITH table_value1 AS (
         SELECT 
            table1.group_name,
            table1.value1,
           FROM table1
        ), table_value2 AS (
         SELECT 
            table2.group_name,
            table2.value2,
           FROM table2
        ), TOTAL_groups AS (
         SELECT 
            'value1'::text AS group_name,
             sum(xy_table."value1")::numeric as results
           FROM xy_table
        UNION ALL
         SELECT 
            'value2'::text AS group_name,
             sum(xy_table."value2")::numeric as results
           FROM xy_table
        UNION ALL
         SELECT 
            'unknown'::text AS group_name,
             sum(xy_table."unknown")::numeric as results
           FROM xy_table
        ), TOTAL AS (
         SELECT 
            TOTAL_groups.group_name,
            TOTAL_groups.results
            FROM TOTAL_groups
        UNION ALL           
            'TOTAL'::text AS group_name,
            round(sum(TOTAL_groups.raba), 1) as results
           FROM skupaj_energenti
        )
 SELECT 
    a.group_name,
    COALESCE(a.results, 0::numeric) AS value1,
    COALESCE(a.results, 0::numeric) AS value2
  FROM table_value1 a
     LEFT JOIN table_value2 b ON b.group_name = a.group_name
     LEFT JOIN TOTAL c ON f.group_name = a.group_name
WITH DATA;

I have no idea how should I write such conditions in SQL. Please help.

Answer

Distribute ‘unknown’ row to other rows. Assuming value1, value2 are DECIMAL

select group_name, value1 * (1 + k1) value1, value2 * (1 + k2) value2
from tbl
cross join (
    select sum(case group_name when 'unknown' then value1 end) / sum(case group_name when 'TOTAL' then value1 else -value1 end)  k1,
           sum(case group_name when 'unknown' then value2 end) / sum(case group_name when 'TOTAL' then value2 else -value2 end)  k2
    from tbl
    where group_name in ('TOTAL', 'unknown')
    ) t
where tbl.group_name not in ('TOTAL', 'unknown')

db<>fiddle

Advertisement

Answer