8
Pointer
7y

Does anyone knows about OLAP cubes, Rollups and whatsoever?

I'm wondering if this query it's overcomplicated...

Just trying to achieve a sum of amounts by month on a year lapse

Comments
  • 1
    @Pointer I don't know anything about the things you ask, but you get a +1 for the font ligatures and the colour scheme. What scheme is it, if I may ask?
  • 1
    @Qaldim It's Material Theme for JetBrains products, font is Fira Code, that font it's damn sexy though
  • 3
    Ugh I really hate sql :/ it’s hard sometimes I don’t get what’s being asked...
  • 1
    @Pointer FiraCode is my goto font as well! 😁
  • 1
    Just an average query, nothing overcomplicated.

    Only thing, is that DISTINCT (i). I suppose you want distinct by i? Because that doesn't work. DISTINCT when used in this way will find a distinct on all columns, and in this case, when your month_sum could be a floating point, you might end up in approximation problems.

    Anyway, if you want distinct by i, you must write DISTINCT ON (i) i, date_part......

    Also, this query can be speed up by not doing a LATERAL with a subselect basically, but rather a join.
    Because in this case it will perform the query multiple times, while when joining it will be just 1 time, and then a join.
  • 0
    @AndSoWeCode I tried with a subselect, but It wasn't returning what I wanted, maybe I was doing it wrong.

    What I want to achieve is that given a date, the query will return the sum of the amount for each month, or 0 if there's no record matching the criteria (being that it sums all the rows that the "date" field falls between the timespan of a given month).

    I know about Lateral and how it works, but being honest, I was just trying to get the work done, because deadlines, good thing is that I have more time now and I'll keep checking on this.
Add Comment