I have a transaction table with 3 months data:

query:

SELECT a.branch,
       a.dt_acc,
       a.ct_id,
       TRUNC(a.date, 'MONTH'),
       COUNT(*) a_count
  FROM a_transactions a
 WHERE a.v_date BETWEEN '01.03.2019' and '31.05.2019'
 GROUP BY a.branch, a.dt_acc, a.ct_id, TRUNC(a.date, 'MONTH');

result:

For each month, a_count printed one record. I need a_count to count the amount for previous months, that is, if in March a_count was equal to 1, in April there is also a record, a_count should already be 2, and if there is also a record in May, then a_count should be 3

I need this result:

CodePudding user response：

What you describe here is exactly sliding window functions. Syntax may vary a bit depending on your sql dialect, but generally you can use:

select a.branch, a.dt_acc, a.ct_id, trunc(a.date,'MONTH'), 
count(*) over (order by a.date rows between unbounded preceding and current row) a_count
from a_transactions a
where a.v_date between '01.03.2019' and '31.05.2019'
group by a.branch, a.dt_acc, a.ct_id, trunc(a.date,'MONTH');

CodePudding user response：

You can use cummulative sum like this:

WITH YOUR_SQL AS (
        SELECT A.BRANCH, A.DT_ACC, A.CT_ID, TRUNC(A.DATE,'MONTH') MM , COUNT(*) A_COUNT
FROM A_TRANSACTIONS A
WHERE A.V_DATE BETWEEN '01.03.2019' AND '31.05.2019'
GROUP BY A.BRANCH, A.DT_ACC, A.CT_ID, TRUNC(A.DATE,'MONTH')
    )
SELECT Y.* , SUM (A_COUNT) OVER (ORDER BY MM,BRANCH,DT_ACC,CT_ID) CUM_SUM
FROM YOUR_SQL Y

CodePudding user response：

You can directly use(without neding a subquery) SUM(COUNT()) OVER () as an analytic function such as

SELECT branch,
       dt_acc,
       ct_id,
       TRUNC("date", 'YEAR'),
       TRUNC("date", 'MONTH'),
       COUNT(*) AS a_count,
       SUM(COUNT(*)) OVER (ORDER BY TO_CHAR("date", 'YYYYMM')) AS a_count_sum
  FROM a_transactions
 WHERE v_date BETWEEN date'2019-03-01' and date'2019-05-31'
 GROUP BY branch, dt_acc, ct_id, TRUNC("date", 'YEAR'), TRUNC("date", 'MONTH')

where

• using date'yyyy-mm-dd' format is a good habit in order to prevent date literal issues independent of what the date settings for the DB
• date is a reserved word and cannot be used as column name unless quoted(eg.your column presumed to be "date")
• considering the other years would also be a good idea. Perhaps your upcoming data will propagate for more than one year.