How to calculate a Cumulative total using SQL

Question

I have a Tickets table in My database , each Ticket have a status_id (1,2,3)

1:  Ticket IN PROGRESS
2:  Ticket Out Of time
3:  Ticket Closed 

• I want using SQL to calculate the number of tickets for each status .

• Calculate the cumulative total for each Status in a specific Date, I have already a column affectation_Date that contains the date where the status of ticket has been changed .

Answer 1

Use conditional aggregation as

SELECT TicketID,
       AffectationDate,
       SUM(CASE WHEN StatusID = 1 THEN 1 ELSE 0 END) InProgress,
       SUM(CASE WHEN StatusID = 2 THEN 1 ELSE 0 END) OuOfTime,
       SUM(CASE WHEN StatusID = 3 THEN 1 ELSE 0 END) Closed,
       COUNT(1) Total
FROM Tickets
GROUP BY TicketID,
         AffectationDate
ORDER BY TicketID,
         AffectationDate;

Or if you want to GROUP BY AffectationDate only

SELECT AffectationDate,
       SUM(CASE WHEN StatusID = 1 THEN 1 ELSE 0 END) TotalInProgress,
       SUM(CASE WHEN StatusID = 2 THEN 1 ELSE 0 END) TotalOutOfTime,
       SUM(CASE WHEN StatusID = 3 THEN 1 ELSE 0 END) TotalClosed,
       COUNT(1) TotalStatusThisDate
FROM Tickets
GROUP BY AffectationDate
ORDER BY AffectationDate;

Live Demo

Answer 2

Using conditional counts.

SELECT affectation_Date,
COUNT(CASE WHEN status_id = 1 THEN 1 END) AS TotalInProgress,
COUNT(CASE WHEN status_id = 2 THEN 1 END) AS TotalOutOfTime,
COUNT(CASE WHEN status_id = 3 THEN 1 END) AS TotalClosed
FROM Tickets t
GROUP BY affectation_Date
ORDER BY affectation_Date

Answer 3

you may use the desired filter condition for the date criteria

SELECT COUNT(1), STATUS 
FROM tickets
WHERE affectation_Date >= 'someDate'
group by status

Regards

Answer 4

You just need to group by status and count the number of tickets in each group:

select status, count(*) as number
from Tickets
where dt >= '2019-01-01 00:00:00' and dt < '2019-01-02 00:00:00'
group by status
having status >= 1 and status <= 3

Answer 5

This adds the Cumulative Sum to the existing answers:

SELECT AffectationDate,
       Sum(CASE WHEN StatusID = 1 THEN 1 ELSE 0 END) AS TotalInProgress,
       Sum(CASE WHEN StatusID = 2 THEN 1 ELSE 0 END) AS TotalOutOfTime,
       Sum(CASE WHEN StatusID = 3 THEN 1 ELSE 0 END) AS TotalClosed,
       Count(*) as TotalStatusThisDate,
       Sum(Sum(CASE WHEN StatusID = 1 THEN 1 ELSE 0 END)) Over (ORDER BY AffectationDate) AS cumTotalInProgress,
       Sum(Sum(CASE WHEN StatusID = 2 THEN 1 ELSE 0 END)) Over (ORDER BY AffectationDate) AS cumTotalOutOfTime,
       Sum(Sum(CASE WHEN StatusID = 3 THEN 1 ELSE 0 END)) Over (ORDER BY AffectationDate) AS cumTotalClosed,
       Sum(Count(*)) Over (ORDER BY AffectationDate) AS cumTotalStatusThisDate
FROM Tickets
GROUP BY AffectationDate
ORDER BY AffectationDate;