Stories
Slash Boxes
Comments

Dev.SN ♥ developers

posted by martyb on Thursday July 09 2020, @09:10AM   Printer-friendly
Paste your code into a comment on this story.
This discussion has been archived. No new comments can be posted.
Display Options Breakthrough Mark All as Read Mark All as Unread
The Fine Print: The following comments are owned by whoever posted them. We are not responsible for them in any way.
(1)
  • (Score: 0) by Anonymous Coward on Thursday July 09 2020, @10:02AM (1 child)

    by Anonymous Coward on Thursday July 09 2020, @10:02AM (#31616)
    The inner query is effectively a list of <y,m,v> tuples, v=v(y,m) for year=y in range 2014..., month=m.
    What I was after is a list of <m, v(y=2014,m), v(y=2015,m), ..., v(y=2020,m)>
    This works by summing lots of things that are 0 almost everywhere:

    SELECT
      MONTHNAME(rawdate),
      m,
      SUM(if(y=2014,v,0)) as v2014,
      SUM(if(y=2015,v,0)) as v2015,
      SUM(if(y=2016,v,0)) as v2016,
      SUM(if(y=2017,v,0)) as v2017,
      SUM(if(y=2018,v,0)) as v2018,
      SUM(if(y=2019,v,0)) as v2019,
      SUM(if(y=2020,v,0)) as v2020
    FROM
        (SELECT ... AS rawdate,
            YEAR(...) AS y,
            MONTH(...) AS m,
            ROUND((SUM(...)),2) AS v
        FROM ymv_table
        GROUP BY y,m
        ) as ymv
    GROUP BY m
    ORDER BY m
    • (Score: 2) by martyb on Thursday July 09 2020, @10:18AM

      by martyb (76) Subscriber Badge on Thursday July 09 2020, @10:18AM (#31617) Journal

      You know what the actual data looks like; nobody else does. That makes this rather difficult. It would help if you could provided the following:

      1. SELECT * FROM year;
      2. SELECT * FROM month;
      3. SELECT * FROM ymv_table;
      4. the result of the provided query
(1)