Case statement bucketing in Sybase Sql

Question

I have a data set which looks like this:

month      year       total_sales
01         2014       4567889
02         2014       5635627
03         2014       997673
04         2014       2134566
05         2014       2666477

My goal is to create a YTD function on the above dataset. Eg: If I want the 01 month data to display, it should give the total sales for 01 month. If i want the 02 month to display, it should give me the total sales for 01 + 02 month combined and so on for the other months.

The query i wrote goes as follows:

select year, case when month in ('01') then 'JAN'
              when month in ('01','02') then 'FEB'
              -
              -
              -
              when month in ('01','02','03','04','05') then 'MAY'
              end as bucket, sum(total_sales) from <table_name>
              group by year, case when month in ('01') then 'JAN'
              when month in ('01','02') then 'FEB'
              -
              -
              -
              when month in ('01','02','03','04','05') then 'MAY'
         end

The result set it fetches, doesn't add up the total sales as a YTD function but instead shows the total sales for that particular month only. I can create the Pivot table view for the required data set but that is not how i need it because i need to build reports on the data set.

Can someone help me with the concept if i am missing something?

Thanks in advance.

Answer 1

Perhaps a correlated subquery would help:

select t.*,
       (select sum(total_sales)
        from table t2
        where t2.year = t.year and
              t2.month <= t.month
       ) as YTD
from table t;

Answer 2

Here is another solution:

WITH months AS (
  SELECT month, year
  FROM <table_name>
)
SELECT m.month, m.year, SUM(t.total_sales)
FROM months m JOIN <table_name> t ON t.year=m.year AND t.month<=m.month
GROUP BY m.year, m.month
ORDER BY m.year, m.month;