CODEWITHSUNDEEP
sqlsql-servert-sql
Bekri Fatima Azzahrae
Bekri Fatima Azzahrae

Reputation: 15

How to calculate a Cumulative total using SQL

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

Upvotes: 0

Views: 142

Answers (5)

dnoeth
dnoeth

Reputation: 60462

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;

Upvotes: 0

Lajos Arpad
Lajos Arpad

Reputation: 76414

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

Upvotes: 0

Ilyes
Ilyes

Reputation: 14928

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

Upvotes: 1

TheZerg
TheZerg

Reputation: 329

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

Upvotes: 0

LukStorms
LukStorms

Reputation: 29647

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

Upvotes: 0

Related Questions