how to make this union query more effecient?

Question

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

Answer 1

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)