Running count grouped by month to aggregate sales

Question

I have sales table with fields Datatime(DateTime) and sales(Number). How do I write a SQL query to fetch data grouped by month year for last 6 months with a new column 'running count'.

Running count is a positive whole number which starts with 1 (if sales else 0) then 2 if there is a sale for next month (given there is a sale and running count was 1 for the previous month) and so on. If there is no sales for a month running count will be 0 for that month and if there is a sale for next month it restarts with 1 and so on.

Example:
Current month year is '09/2017' and there are sales for March, April, June, July, August but no sales (no row) for the month of May. So the output will be:

| Month_Year | Sum(Sales) | Running_Count |
| 03/2017    | 1000       | 1             |
| 04/2017    | 1500       | 2             |
| 05/2017    | 0          | 0             |
| 06/2017    | 300        | 1             |
| 07/2017    | 550        | 2             |
| 08/2017    | 1900       | 3             |

Suggestions are welcomed to edit this question.

Maybe this question will help you https://stackoverflow.com/questions/8345282/sql-server-group-by-month — kenfire, Sep 11 '17 at 12:42
A sidenote: FLOAT??? Why use an approximate datatype when you have precise types (namely NUMBER) available? Are the 38 possible digits in NUMBER not sufficient for your immense sales figures? I suggest you change this. Don't ever use FLOAT when not absolutely necessary. — Thorsten Kettner, Sep 11 '17 at 13:04
@kenfire I know how to group by month. My problem is how to achieve running count part. — Ayush Kesarwani, Sep 11 '17 at 13:22
@NikNik Question is how to get running count column in Oracle SQL. — Ayush Kesarwani, Sep 11 '17 at 13:24
@KaushikNayak I am working on creating a function which will store the current value which will increment in case of some value of sales otherwise will return zero and will have a while loop with control flag. Problem is there isn't a row when no sales. — Ayush Kesarwani, Sep 11 '17 at 16:37

score 1 · Answer 1 · answered Sep 11 '17 at 17:47

This is ugly... but it works:

WITH
    aset
    AS
        (SELECT month_year
              , sales
              , CASE WHEN COALESCE (LAG (sales) OVER (ORDER BY month_year), 0) = 0 THEN 1 ELSE 0 END COUNT
           FROM deleteme_table),
    bset
    AS
        (SELECT month_year, sales, SUM (COUNT) OVER (ORDER BY month_year) count_group
           FROM aset)
SELECT month_year
     , sales
     , CASE
           WHEN sales = 0
           THEN
               0
           ELSE
               ROW_NUMBER ()
                   OVER (
                       PARTITION BY count_group ORDER BY month_year
                   )
       END
           running_count
  FROM bset;

SALES  MONTH_YEAR RUNNING_COUNT                          
1000   2017/03/01     1                                      
1500   2017/04/01     2                                      
0      2017/05/01     0                                      
300    2017/06/01     1                                      
550    2017/07/01     2                                      
1900   2017/08/01     3

Running count grouped by month to aggregate sales

1 Answers1