CODEWITHSUNDEEP
sql-server
user12836325
user12836325

Reputation: 1

SQL query to retrieve count of Status Column

I have a table with a Status column which can have values such as 

Completed, Failed, Started

I want query to retrieve the count of completed records, count of failed records and count of started records on a particular day:

ID    Status        Last_Updated
---------------------------------------
1     Completed     2020-02-03 15:54:59
2     Failed        2020-02-03 13:54:59
3     Started       2020-02-03 11:24:59
4     Completed     2020-02-03 15:24:59
5     Completed     2020-02-03 13:54:59
6     Started       2020-02-03 11:44:59

Expected output:

Count(status)   Status
----------------------------
3               Completed
1               Failed
2               Started

Upvotes: 0

Views: 1547

Answers (3)

EmreB.
EmreB.

Reputation: 61

Solution:

DECLARE @table TABLE(
    ID int identity(1,1),
    Status nvarchar(100),
    Last_Updated datetime
)
INSERT INTO @table(Status,Last_Updated) VALUES
('Completed','2020-02-03 15:54:59'),
('Failed'   ,'2020-02-03 13:54:59'),
('Started'  ,'2020-02-03 11:24:59'),
('Completed','2020-02-03 15:24:59'),
('Completed','2020-02-03 13:54:59'),
('Started'  ,'2020-02-03 11:44:59');
SELECT COUNT(Last_Updated) 'Count(status)',Status  FROM @table GROUP BY Status

This is output:
Count(status)   Status
3   Completed
1   Failed
2   Started

Upvotes: 0

Suraj Kumar
Suraj Kumar

Reputation: 5643

You can also use the sum(1) function as shown below.

create table WorkStatus (Id int, Status Varchar(20), Last_Updated datetime)
Insert into WorkStatus Values(1, 'Completed', '2020-02-03 15:54:59'),
(2, 'Failed', '2020-02-03 13:54:59'),
(3, 'Started', '2020-02-03 11:24:59'),
(4, 'Completed', '2020-02-03 15:24:59'),
(5, 'Completed', '2020-02-03 13:54:59'),
(6, 'Started', '2020-02-03 11:44:59')

Select 
     Status,
     sum(1) as TotalCount         
     from WorkStatus
group by Status order by Status

Here is the output.

Status TotalCount
-----------------   
Completed 3 
Failed    1 
Started   2

You can also use Row_Number() and Subquery as shown below.

--Using Row_Number and Subquery
Select status, max(SrNo) as TotalCount from(
Select Status, 
       Row_Number() Over(Partition By Status Order By Status) as SrNo
from WorkStatus
)a group by status

Here is the db<>fiddle live demo.

Upvotes: 0

mkRabbani
mkRabbani

Reputation: 16908

You can achieve this with a simple query with GROUP BY and Aggregation as below-

SELECT COUNT(*),Status
FROM your_table
GROUP BY Status

To get the results for a particular day, you can just add WHERE clause as below-

SELECT COUNT(*),Status
FROM your_table
WHERE CAST(Last_Updated AS DATE) = '2020-02-03'
GROUP BY Status

Upvotes: 1

Related Questions