How to get cumulative totals

Question

In following example i'm getting daily totals. I need to get cumulative totals relevant to the DPetAcNo.

    SELECT
DPetAcNo as AcNo,
SUM(DPetAmount) as PetTotal
FROM PettyDetail
WHERE DPetComCode='15'
and DPetLocCode='01'
and DPetDate=CONVERT(date,'20181113',111)
group by
DPetAcNo

Possible duplicate of [SQL Server Cumulative Sum by Group](https://stackoverflow.com/questions/17971988/sql-server-cumulative-sum-by-group) — David Manheim, Nov 14 '18 at 11:00
Be ***very*** clear about the exact output you want. Show us an example of the results you need. *(Add them to the question by editing the question, don't add a comment or an answer.)* — MatBailie, Nov 14 '18 at 11:05

score 0 · Answer 1 · answered Nov 14 '18 at 12:12

The canonical way to solve this is:

SELECT DPetAcNo as AcNo, SUM(DPetAmount) as PetTotal,
       SUM(SUM(DPetAmount)) OVER (ORDER BY DPetAcNo) as Running_PetTotal
FROM PettyDetail
WHERE DPetComCode = '15' AND
      DPetLocCode = '01' AND
      DPetDate = '20181113'
GROUP BY DPetAcNo;

If this does not do what you want, then you need to be quite clear on what doesn't work and on what result set you actually want.

How to get cumulative totals

1 Answers1