Showing data for 5 Years Interval of products.
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?
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.
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;
WHERE according to the years you need.