Search code examples
sqlcountsumcase

Best way to merge two SQL queries into one query

I have two small SQL queries that I would like to merge to be a single query that I will eventually use in a stored procedure. I have tried a CASE and IF/ELSE but could not get it to work. Below is the code. What I want to achieve is have the first column 'Count of Open Order Lines' based on both where statements from both queries but the second column 'Sum Quantity' only based on the where statement from the second query.

select 
COUNT(OO.ORNO) AS 'Count of Open Order Lines'
from OOLINE OO
    join OOHEAD OH
    on OO.CONO = OH.CONO
    and OO.ORNO = OH.ORNO
where OO.ORST BETWEEN 22 AND 66
    and OH.OBLC BETWEEN 1 AND 8;


select 
CAST(SUM(OO.ORQT) AS DECIMAL (8,0)) AS 'Sum Quantity'
from OOLINE OO
    join OOHEAD OH
    on OO.CONO = OH.CONO
    and OO.ORNO = OH.ORNO
where OO.ORST BETWEEN 22 AND 66;
Solution

  • You need conditional aggregation:

    SELECT COUNT(CASE WHEN OH.OBLC BETWEEN 1 AND 8 THEN OO.ORNO END) AS "Count_of_Open_Order_Lines",
       CAST(SUM(OO.ORQT) AS DECIMAL (8,0)) AS Sum_Quantity
FROM OOLINE OO INNER JOIN OOHEAD OH
ON OO.CONO = OH.CONO AND OO.ORNO = OH.ORNO
WHERE OO.ORST BETWEEN 22 AND 66;

    In the WHERE clause I kept only the condition OO.ORST BETWEEN 22 AND 66 which is common in both queries and for Count of Open Order Lines I used a CASE expression based on the condition OH.OBLC BETWEEN 1 AND 8.