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

(1) Tag your question with the database you are using. (2) Qualify all column references so it is clear what your query is doing. (3) Specify better table names. Or are both subqueries really doing self joins on the same table? — Gordon Linoff
– Gordon Linoff, Commented Feb 25, 2021 at 15:30
Hi Gordon! 1) - done. 2) not sure what to add here, hopefully things are clear? 3) done, updated the join. I edited the names from the real query, to try and make it more readable. Hopefully I didn't make it worse. My question is more in the direction > is this something that a Case query can manage, for example. But I can't wrap my head around that... — egg
– egg, Commented Feb 25, 2021 at 15:34

Gordon Linoff · Accepted Answer · 2021-02-25 15:32:12Z

2

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)

answered Feb 25, 2021 at 15:32

Gordon Linoff

1.3m62 gold badges705 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

egg Over a year ago

thanks Gordon, it worked! Very nice and very easy :)

Collectives™ on Stack Overflow

how to make this union query more effecient?

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related