sqlunionadvantage-database-server

how to make this union query more effecient?


I've created the SQL query below, using a UNION join.

As you can see, I query the same table 2 times, with slightly different criteria. If TYP is A then it's an Offer. If TYP is N and AB is true, then it's an order.

The whole thing works. But I'm guessing there's a more elegant way to do this? I'm hoping you can tell me what that way is! :)

Thanks. (btw, using advantage sql if it makes a difference)

SELECT
DATUM as report_month,
REGION as region,
count(DISTINCT NUMMER) as order_number,
'Offer' as Type
from xxxxxxxxxxxx
left join xxxxxxxxx on KDNR = anotherDAB.KDNR
WHERE DATUM = '2021-02-16' AND TYP = 'A' 
group by report_month, region

UNION

SELECT
DATUM as report_month,
REGION as region,
count(DISTINCT NUMMER) as order_number,
'Order' as Type
from xxxxxxxxxxx
left join xxxxxxxxx on KDNR = anotherDAB.KDNR
WHERE DATUM = '2021-02-16' AND TYP = 'N' AND AB = true 
group by report_month, region

Solution

  • I suspect you just want conditional aggregation:

    SELECT DATUM as report_month, REGION as region,
           count(DISTINCT NUMMER) as order_number,
           typ
    from xxxxxxxxxxxx left join
         xxxxxxxxx
         on KDNR = KDNR
    WHERE DATUM = '2021-02-16' AND
          (TYP = 'A' OR TYP = 'N' AND AB = true)
    GROUP BY report_month, region, type;
    

    The above leaves TYP as A or N. You can use a case expression if you want strings:

    (CASE WHEN typ = 'A' THEN 'Offer' ELSE 'Order' END)