How to merge Two queries into one and result in one row

Question

SELECT lp.lead_bucket_no , case when p.product = 'S-400' then qty end as 'S400' , case when p.product = 'Dish Antenna' then qty end as 'DishAntenna' FROM lead_products lp INNER JOIN products p ON p.product_id = lp.product_id WHERE type = 'stock' GROUP BY lead_bucket_no ORDER BY lp.lead_bucket_no

and

SELECT lp.lead_bucket_no , case when p.product = 'S-400' then qty end as 'S400' , case when p.product = 'Dish Antenna' then qty end as 'DishAntenna' FROM lead_products lp INNER JOIN products p ON p.product_id = lp.product_id WHERE type = 'order' GROUP BY lead_bucket_no ORDER BY lp.lead_bucket_no

how to merge these both query and and got record in single row. only type are different in both queries. enter image description here

Answer 1

I think you just want OR (or IN) in the WHERE clause:

SELECT lp.lead_bucket_no, 
       SUM(case when p.product = 'S-400' then qty end) as S400,
       SUM(case when p.product = 'Dish Antenna' then qty end) as DishAntenna 
FROM lead_products lp INNER JOIN
     products p
     ON p.product_id = lp.product_id
WHERE type IN ('stock', 'order')
GROUP BY lead_bucket_no
ORDER BY lp.lead_bucket_no;

You also need some sort of aggregation for the expressions that are not in the GROUP BY clause. You may also want to aggregate by TYPE. It is unclear what you want for the final output.