Kein Weltraum links am Gerät? Kein Problem dieses Buch die Lösung:
This be book bad translation, video games! - Fangamer
#GoodOmens fertig geguckt. War gut.
Of course that question then results in the idea that one SELECT had duplicates, the other one had some as well, and by UNIONizing them those were dropped.
But they weren't. I had tried. The UNIONized SELECTs gave the right number of results. Only the PIVOT dropped them.
So an hour later I'm back to the explanation that feels counter-intuitive.
Also it's Saturday and I need #SQL exclusively at work!
I was flabbergasted, but eventually found out that UNION "removes duplicates", so I changed it to UNION ALL (which doesn't) and got the right number.
That solution felt and still feels counter-intuitive in my mind. The PIVOT removes the "state" from each result so some might become duplicates of each other. But surely the PIVOT happens *after* the UNION right? After all, executing the two SELECTs without the PIVOT got the right number of results.
... I think I just nerd sniped myself.
A few days ago I wrote some #SQL at work. I needed to know the number of things per state. Sounds like a PIVOT, the only problem was that I couldn't find the two states I wanted in one SELECT statement. So I used two and UNIONized them. The number of results in the UNIONized SELECTs looked about right, but when PIVOTed (using COUNT) them the sums of the things didn't add up to the same number!
Anyway #np Porcupine Tree - The Incident
Generalistic and moderated instance.