Postgres row transformation using case-CodePudding

TABLE 1

KEY	VALUE
1	21
1	32
2	24
3	22
1	42
3	20
4	27
4	28
5	29
6	31
7	45
5	51
8	33

TABLE 2

CODE	KEY
AA	1
AB	2
AC	3
AD	4
AE	5
AF	6
AG	7
AH	8

Table 1 contains key and its corresponding values, a key can have multiple values. While Table 2 is a master table that contains a unique key corresponding to a unique code.

Here i want to transform the table in such a way that AB value is summed to AA ,similarly AG's value is summed with AD. New AA = AA AB and AD=AD AG. Table 1 has 1 thousand records.

codes have to be groupby generating total values and above two specific code values have to be summed simultaneously in final table.

Key column is common in both tables. I have shared the desirable output below. pls help or tell me the approach.

CODE	VALUE	KEY
AA	119	1
AC	22	3
AD	100	4
AE	29	5
AF	31	6
AH	33	8

SELECT t2.code, t1.value, t1.key
FROM (
  SELECT CASE code 
           WHEN 'AB' THEN 'AA' 
           WHEN 'AG' THEN 'AD' 
           ELSE code 
         END code, 
         SUM(value) "value"
  FROM table1
  GROUP BY 1
) t1 INNER JOIN table2 t2
ON t2.key = t1.key
ORDER BY t1.code;

the above code didnt helped pls do suggest.

CodePudding user response：

You need to slightly change your query like this:

SELECT t2.code, SUM(t1.value) value, t1.key
FROM (
  SELECT CASE code 
           WHEN 'AB' THEN 'AA' 
           WHEN 'AG' THEN 'AD' 
           ELSE code 
         END code, 
         value
  FROM table1
) t1 INNER JOIN table2 t2
ON t2.key = t1.key
GROUP BY t2.code, t1.key
ORDER BY t1.code;

CodePudding user response：

You can do something like this:

  with cte as (select  case 
         when code = 'AB' then 'AA'
         when code = 'AG' then 'AD'
         else code
         end, value
    from table_2 t2 left join table_1 t1 on (t2.key = t1.key))
 
    select code, sum(value) from cte group by code

Demo in DBfiddle

CodePudding user response：

Because your code in table2 but value in table1

so that We can try to use the aggregate function JOIN by table1 and table2, then join table2 again

WITH CTE AS (
  SELECT CASE code 
           WHEN 'AB' THEN 'AA' 
           WHEN 'AG' THEN 'AD' 
           ELSE code 
       END code,
       SUM(t1.value) sumValue
  FROM table1 t1 
  INNER JOIN table2 t2
  ON t2.key = t1.key
  GROUP BY CASE code 
           WHEN 'AB' THEN 'AA' 
           WHEN 'AG' THEN 'AD' 
           ELSE code 
         END
)
SELECT c.*,t2.key
FROM CTE c 
INNER JOIN table2 t2
ON c.code = t2.code

sqlfiddle