3 ms·
At work I ended up with something similar. A reporting query has about 20 sum()'ed columns, and found the easiest and by far fastest was just to have 20+ querie
by magicalhippo 6y ago
At work I ended up with something similar. A reporting query has about 20 sum()'ed columns, and found the easiest and by far fastest was just to have 20+ queries glued by UNION ALL, and aggregate the sums over that.
It's heavy in it's raw form, but if you add some search terms to narrow things down the DB really cuts through.
It's about 320 lines of SQL, so to make debugging a bit easier I added a dummy "identifier" column to each of the union'ed sub-queries, so first would have value 100, second 200 etc, so I could quickly identify which of the sub-queries produced a given row. Not counting in 1's allowed for easy insertion of additional sub-queries near relevant "identifiers".