SQLite Forum

Showing data for 5 Years Interval of products.

Showing data for 5 Years Interval of products.

(1) By anonymous on 2020-08-30 04:37:35 [link] [source]

I have a product, year, and sales columns where different products are shown for each year with their corresponding year and sales from 2000 to 2018.

I have to show avg, min, and max sales of each Product for 5 years interval.

Also, the query should omit the ‘0’ values when calculating the average and minimum value of the product.

How can we achieve it?

(2) By Keith Medcalf (kmedcalf) on 2020-08-30 05:23:20 in reply to 1 [link] [source]

Write an SQL statement to provide the data you need.

Data you have not provided which would be needed to make suggestions include:
(a) the table that contains the source data (as in the create table statement)
(b) the definition of what constitutes a "5 year interval" such as when does it begin
(c) do you know how to write a program to write the report once the data is provided

This is the absolute minimum data required if you want any answer other than that you need to write SQL queries to extract the data that you want so that you can write a program to run those queries and output the resulting report.

(3) By anonymous on 2020-08-30 09:22:02 in reply to 1 [source]

Something like this:

create table sales(product,year,sales);

insert into sales values('A','2000',0);   -- excluded because zero
insert into sales values('A','2001',10);
insert into sales values('A','2002',20);
insert into sales values('A','2003',20);  -- excluded by year range

insert into sales values('B','2000',5);
insert into sales values('B','2001',10);
insert into sales values('B','2002',0);   -- excluded because zero
insert into sales values('B','2003',100); -- excluded by year range

select product,min(sales),avg(sales),max(sales) from sales
  where year between '2000' and '2002'
    and sales > 0
  group by product;

Adjust WHERE according to the years you need.