group by postgres in every id like

Question

Can someone help me to solved my issue , i have 2 table

Query for table one :

  SELECT (REPLACE(A .coano, '0', '')) coano_for_like FROM glfcoa_dump A WHERE posum = 'K' ORDER BY A .coano ASC;

Result for table one :

coano_for_like 
----------------
1
11
12
13
131
132
2
21
211
212
22
221
2211
292
2921
2922
2923
2924
293

Query for table two :

SELECT
    coano,
    SUM (CAST(endbal AS NUMERIC)) endbal
FROM
    vw_fx_glfmas
WHERE
    OFCID = '450'
AND TGL = '2017-08-11' and coano like '11%'
GROUP BY 
    coano
ORDER BY
    SUBSTRING (coano ,- 2, 7) ASC

Result For table two :

coano   endbal
--------------
111000  0
112000  234342846
114000  0

in table two coano like '11%' 11 is result from table one

my question , how to group by all coano like in table two from result table one ?

i want result like this :

coano_for_like  coano   endbal
----------------------------------
1               111000  0
                112000  234342846
                114000  0
                152101  813307251
                152102  49259791
                162110  -3456957
                163010  0
                169100  -36826000
                169200  -32649000
11              111000  0
                112000  234342846
                114000  0
12              ...     ...

Answer 1

You can use like in a join.

SELECT coano_for_like, coano, SUM (CAST(endbal AS NUMERIC)) endbal
  FROM vw_fx_glfmas 
  JOIN (SELECT (REPLACE(A .coano, '0', '')) coano_for_like FROM glfcoa_dump A WHERE posum = 'K') r
 WHERE OFCID = '450' AND TGL = '2017-08-11' and coano like coano_for_like || '%'
 GROUP BY coano
 ORDER BY coano ASC

slightly correcting

SELECT
    r.coano_for_like,
    coano,
    SUM (CAST(endbal AS NUMERIC)) endbal
FROM
    vw_fx_glfmas a,
    (
        SELECT
            (REPLACE(A .coano, '0', '')) coano_for_like
        FROM
            glfcoa_dump A
        WHERE
            posum = 'K'
    ) r
WHERE
    ofcid = '450'
AND TGL = '2017-08-11'
AND coano LIKE coano_for_like || '%'
GROUP BY
    coano_for_like,
    a.coano
ORDER BY
    coano_for_like ASC