postgresql conbine two query-CodePudding

Query 1

SELECT cpl.product_id
FROM crm_planning_line cpl
    LEFT JOIN crm_planning cp ON cpl.crm_planning_id = cp.id 
WHERE EXTRACT(MONTH FROM cp.planning_date   interval '7' HOUR)=9
    AND EXTRACT(YEAR FROM cp.planning_date   interval '7' HOUR)=2022
    AND cp.salesman_id = 27
    AND cp.customer_id = 2977

Result 1

product_id | view
7359         1
8192         1
7359         1
8192         1
7357         1

Query 2

SELECT line.product_id
FROM account_invoice_line line
    LEFT JOIN account_invoice inv on inv.id=line.invoice_id
WHERE EXTRACT(MONTH FROM inv.date_invoice)=9
    AND EXTRACT(YEAR FROM inv.date_invoice)=2022
    AND inv.user_id=27
    AND inv.partner_id=2977

Result 2

product_id | buy
7359         100
4970         200
4970         50

For final result, I want to combine those query where the result like this:

product_id | summery
7359         100,2
8192         2
7357         1
4970         250

Final result 2:

product_id | summery
    7359         100,2
    8192         0,2
    7357         0,1
    4970         250,0

Thank you for the help

CodePudding user response：

Sample Data:

CREATE TABLE public.table_view (
    product_id int4 NULL,
    "view" int4 NULL
);

CREATE TABLE public.table_buy (
    product_id int4 NULL,
    buy int4 NULL
);

INSERT INTO table_view (product_id, "view") VALUES(7359, 1);
INSERT INTO table_view (product_id, "view") VALUES(8192, 1);
INSERT INTO table_view (product_id, "view") VALUES(7359, 1);
INSERT INTO table_view (product_id, "view") VALUES(8192, 1);
INSERT INTO table_view (product_id, "view") VALUES(7357, 1);

INSERT INTO table_buy (product_id, buy) VALUES(7359, 100);
INSERT INTO table_buy (product_id, buy) VALUES(4970, 200);
INSERT INTO table_buy (product_id, buy) VALUES(4970, 50);

Sample Query:

select product_id, string_agg(t1.a1, ',')  from 
(
    select product_id, sum("buy")::text a1 from table_buy
    group by product_id
    union all 
    select product_id, sum("view")::text a1 from table_view
    group by product_id

) t1  
group by product_id