2

I have a table, "MyTable" as below:

tool team metric date
tool1 team1 25 1/1/2023
tool1 team1 10 2/1/2022
tool2 team2 20 1/2/2022

I want to aggregate "metric" periodically by years (distinct years as columns) and have the sql query return something like this where value of 'year' column would be the sum of 'metric'

tool team 2023 2022
tool1 team1 25 10
tool2 team2 0 20

Can anyone help me such a query? For the cases, I need to first infer distinct years from the table, and then use that. And this list of distinct years needs to be from a time range. For this example, we could have time range denoted like this: WHERE date between ago(600d) and now(). That 600 value I will receive from the application, that's why the years are not fixed.

I have tried the to use 'pivot' but its not supported in the database engine I am using.

3
  • See the [pivot] tag. Or [pivot-table] in stackoverflow.com .
    – Rick James
    May 2, 2023 at 2:15
  • 1
    Even though it is possible, I suggest you do this kind of operation in the application layer May 2, 2023 at 5:09
  • This data is coming from aws timestream and pivot is not supported there. I can't do it in the application layer either because data is paginated.
    – jenny
    May 2, 2023 at 5:14

1 Answer 1

2

Even though it is possible, I suggest you do this kind of operation in the application layer. If you insist, you can use a conditional aggregate for each year:

SELECT tool, team
     , COUNT(CASE YEAR(date) WHEN 2023 THEN 1 END)
     , COUNT(CASE YEAR(date) WHEN 2022 THEN 1 END)
     , ...
FROM ...
GROUP BY tool, team

Also, avoid using reserved words as identifiers. Use identifiers that describes the attribute, your date column is probably a date of something in particular.

EDIT new requirements:

SELECT tool, team
     , COALESCE(SUM(CASE YEAR(dt) WHEN 2023 THEN metric END), 0) AS y2023
     , COALESCE(SUM(CASE YEAR(dt) WHEN 2022 THEN metric END), 0) AS y2022
FROM t
GROUP BY tool, team

Fiddle

9
  • Thanks! This helps. The one caveat that I have here with my case is that I can't type out the years (2023, 2022, etc) but want to infer it from my table; result of select distinct(YEAR(time)) from ...
    – jenny
    May 2, 2023 at 5:45
  • Updated question to denote the conditionals
    – jenny
    May 2, 2023 at 6:08
  • Not sure I understand. you can join with that, but then you will have to use the year as a dimension (to be able to know which year is which) and then you are almost back to where you started May 2, 2023 at 6:22
  • @jenny edited answer and added fiddle May 2, 2023 at 6:28
  • Yeah doing it on a query is nasty. Basically what I need to be able to do is: ``` COUNT(CASE WHEN YEAR(date) IN (select distinct(YEAR(date)) from MyTable WHERE date between ago(600d) and now()) then 1 end) ```
    – jenny
    May 2, 2023 at 6:29

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.