CODEWITHSUNDEEP
sqloracle-databaseoracle11g
user3361530
user3361530

Reputation: 1

How to calculate the value of a previous row from the count of another column

I want to create an additional column which calculates the value of a row from count column with its predecessor row from the sum column. Below is the query. I tried using ROLLUP but it does not serve the purpose.

select to_char(register_date,'YYYY-MM') as "registered_in_month"
      ,count(*) as Total_count
from CMSS.USERS_PROFILE a
where a.pcms_db != '*' 
group by (to_char(register_date,'YYYY-MM'))
order by to_char(register_date,'YYYY-MM')

This is what i get 

registered_in_month   TOTAL_COUNT
-------------------------------------
2005-01                1
2005-02                3
2005-04                8
2005-06                4

But what I would like to display is below, including the months which have count as 0

registered_in_month   TOTAL_COUNT  SUM
------------------------------------------
2005-01                1           1
2005-02                3           4
2005-03                0           4
2005-04                8           12
2005-05                0           12
2005-06                4           16

Upvotes: 0

Views: 159

Answers (1)

Noel
Noel

Reputation: 10525

To include missing months in your result, first you need to have complete list of months. To do that you should find the earliest and latest month and then use heirarchial query to generate the complete list.

SQL Fiddle

with x(min_date, max_date) as (
  select min(trunc(register_date,'month')),
         max(trunc(register_date,'month'))
  from users_profile
  )
select add_months(min_date,level-1)
from x
connect by add_months(min_date,level-1) <= max_date;

Once you have all the months, you can outer join it to your table. To get the cumulative sum, simply add up the count using SUM as analytical function.

with x(min_date, max_date) as (
  select min(trunc(register_date,'month')),
         max(trunc(register_date,'month'))
  from users_profile
  ),
y(all_months) as (
  select add_months(min_date,level-1)
  from x
  connect by add_months(min_date,level-1) <= max_date
  )
select to_char(a.all_months,'yyyy-mm') registered_in_month,
       count(b.register_date) total_count,
       sum(count(b.register_date)) over (order by a.all_months) "sum"
from y a left outer join users_profile b
         on a.all_months = trunc(b.register_date,'month')
group by a.all_months
order by a.all_months;

Output:

| REGISTERED_IN_MONTH | TOTAL_COUNT | SUM |
|---------------------|-------------|-----|
|             2005-01 |           1 |   1 |
|             2005-02 |           3 |   4 |
|             2005-03 |           0 |   4 |
|             2005-04 |           8 |  12 |
|             2005-05 |           0 |  12 |
|             2005-06 |           4 |  16 |

Upvotes: 2

Related Questions