1

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.

jenny
  • 11
  • 2

1 Answers1

0

Need some further details like db name and whether the year is only 2022 and 2023 or not. If the number of year is not fixed then pivot will be the option which is could be different based on the db engine.

For fixed number of years and for sql server solution will be:

CREATE TABLE MyTable (
    tool VARCHAR(50),
    team VARCHAR(50),
    metric INT,
    date DATE
);


INSERT INTO MyTable (tool, team, metric, date)
VALUES ('tool1', 'team1', 25, '2023-01-01'),
       ('tool1', 'team1', 10, '2022-02-01'),
       ('tool2', 'team2', 20, '2022-01-02');

Query:

select tool,team,sum(case when year(date)=2023 then metric else 0 end)'2023',
       sum(case when year(date)=2022 then metric else 0 end)'2022'
from MyTable
group by tool,team

Output:

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

fiddle

Solution using pivot in sql server:

Query:

DECLARE @cols AS NVARCHAR(MAX),    
    @query  AS NVARCHAR(MAX)
 
 select @cols=string_agg( quotename(yr),',')within group(order by yr desc) from 
  (select distinct year(date) yr from MyTable)t

set @query = 'SELECT tool,team,' + @cols  + '
             from 
             (
             select tool,team,metric,year([date])yr from MyTable
             ) src
            pivot 
            (
                sum(metric)
                for yr in (' + @cols + ')
            ) piv '

execute(@query)

Output:

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

fiddle

Got the solution for you but for SQL server. You need to convert it to amazon timestream.

Query:

DECLARE @query  AS NVARCHAR(MAX)

set @query = 'select tool,team,'+ (select string_agg(yr,',') from
(select distinct concat('sum(case when year(date)=',year(date), ' then metric else 0 end)',quotename(year(date))) yr
from MyTable)t)+'
  from MyTable
group by tool,team'

execute(@query)

Output:

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

fiddle

Kazi Mohammad Ali Nur Romel
  • 15,301
  • 2
  • 13
  • 26
  • Updated the question. You bring up the right question, I want the distinct years to be inferred from the table, from a certain user provided datetime range, which I am not sure how to feed to the case statement. – jenny May 02 '23 at 05:55
  • @jenny I don't have wrote the query for sql server. But I am afraid that amazon timestream doesn't support pivot but amazon redshift do. (got this conclusion from browsing) – Kazi Mohammad Ali Nur Romel May 02 '23 at 06:28
  • Yeah unfortunately my data is coming from timestream, that's why I have the dynamic date and pagination issue. Basically I want the CASE to be dynamically generated from something like `sum(case when YEAR(date) in (select distinct(YEAR(date)) from MyTable WHERE date between ago(600d) and now()) then metric else 0 end)` – jenny May 02 '23 at 06:36
  • Thank you for all the help though! Its just a nasty use-case/requirement. – jenny May 02 '23 at 06:37