Search code examples
sqlunionsubquery

SQL - Sub select, Union and using functions

I have 2 queries, 1 to derive the overall orders and the other to get the orders which were delivered. The column names are the same as orders and values. only the conditions will vary in the subqueries. I want to get the delivered orders and values separately and the overall orders and values separately. After that, I want to divide the delivered value from the overall value and get it in as a percentage value. There's only one table, only the conditions differ. I want this from 2021-01 to present month wise. This is the example I tried that doesn't work. Please help me.

Example:

Select
a.C_Month,
a.C_Orders/B_Orders as per_orders,
a.C_Value/B_Value as per_Value

from(
select 
to_char(estDate,'yyyymm') as B_Month,
null as area,
count(distinct order_id) as B_Orders,
sum (Value) as B_Value 

from table1
where condition1 is not null
and condition2 is ='1'
and to_char(estDate,'yyyymm')>= '202101' 

group by to_char(estDate,'yyyymm')
order by to_char(estDate,'yyyymm')

union

select
to_char(estDate,'yyyymm') as C_Month,
area,
count(distinct order_id) as C_Orders,
sum (Value) as C_Value 

from table1
where condition1 is not null
and condition2 is ='1'
where condition3 is not null
where condition4 is not null

and to_char(estDate,'yyyymm')>= '202101' 

group by to_char(estDate,'yyyymm')
order by to_char(estDate,'yyyymm')

) as a

group by

a.C_Month,
a.C_Orders/B_Orders as per_orders,
a.C_Value/B_Value as per_Value
;
Solution

  • First, in your example you are asking for "JOIN" but you made an "UNION" :

    JOIN VS UNION

    Trying to follow your logic, you probably need a self-join on B_Month = C_Month.

    QUERY:

      Select
    a.C_Month,
    a.C_Orders/aa.B_Orders as per_orders,
    a.C_Value/aa.B_Value as per_Value
    
    from(
    (select 
    to_char(estDate,'yyyymm') as B_Month,
    null as area,
    count(distinct order_id) as B_Orders,
    sum (Value) as B_Value 
    
    from table1
    where condition1 is not null
    and condition2 is ='1'
    and to_char(estDate,'yyyymm')>= '202101' ) as aa
    
    INNER JOIN
    
    (select
    to_char(estDate,'yyyymm') as C_Month,
    area,
    count(distinct order_id) as C_Orders,
    sum (Value) as C_Value 
    
    from table2
    where condition1 is not null
    and condition2 is ='1'
    where condition3 is not null
    where condition4 is not null
    
    and to_char(estDate,'yyyymm')>= '202101' ) as a
          
    ON aa.B_Month=a.C_Month

    The query compares each row of table1 with each row of table2 to find all pairs of rows which satisfy the join-predicate.

    Remember that "INNER JOIN" can e replaced with an other predicate ( LEFT JOIN, RIGHT JOIN, OUTER JOIN, FULL JOIN) based on your desidered output.