Hello
Given the following data
with my_stat as
(
select 0 sales, '2010' year from duaL
UNION
select 300, '2011' from duaL
UNION
select 0, '2012' from duaL
UNION
select 0, '2013' from duaL
UNION
select 123, '2014' from duaL
UNION
SELECT 333, '2015' FROM DUAL
UNION
SELECT 0, '2016' FROM DUAL
UNION
SELECT 333, '2017' FROM DUAL
) select sales, year
from my_stat
order by year;
I need a query to list sales for each year, and a column with sum of consecutive years without any sale. If there is a sale, the column returns to zero and starts counting again.
Sales Year No_sales
0 2010 1
300 2011 0
0 2012 1
0 2013 2
123 2014 0
333 2015 0
0 2016 1
333 2017 0
(Version: 11g Enterprise Edition Release 11.2.0.3.0)
Thank you for any help