I have a table, "MyTable" as below:
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'
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.