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
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)