CODEWITHSUNDEEP
sql-server
user1432798
user1432798

Reputation: 75

Select query to get total records count in SQL Server

Hi I have a table with status column. How to get total number of records, total number of records with pass status and total number of records with fail status using SQL ?

Upvotes: 0

Views: 10224

Answers (4)

Aaron Bertrand
Aaron Bertrand

Reputation: 280262

A couple of other alternatives (SQL Server 2008+):

DECLARE @foo TABLE([status] CHAR(4));

INSERT @foo SELECT 'pass'
UNION ALL SELECT 'pass'
UNION ALL SELECT 'fail';

-- option #1:

SELECT [status], c = COUNT(*)
FROM @foo
GROUP BY GROUPING SETS(([status]), ());

-- option #2:

SELECT [status], c = COUNT(*)
FROM @foo
GROUP BY [status]
UNION ALL 
SELECT NULL, COUNT(*) OVER() FROM @foo;

Upvotes: 0

automatic
automatic

Reputation: 2737

I would write a query similar to

select 
Count(*) as TotalRecords, 
Sum(case where status = 'Pass' then 1 else 0 end) as PassRecords,
Sum(case where status = 'Fail' then 1 else 0 end) as FaileRecords
from Table

Upvotes: 2

Gordon Linoff
Gordon Linoff

Reputation: 1269563

You can do this with case statements and aggregations:

select count(*), sum(case when status = 'pass' then 1 else 0 end) as pass,
       sum(case when status = 'fail' then 1 else 0 end) as fail
from t

Upvotes: 0

marc_s
marc_s

Reputation: 754348

How about something like this:

SELECT Status, COUNT(*)
FROM dbo.YourTable
GROUP BY Status WITH ROLLUP

This will work if those two statuses (pass, fail) are the only statuses (otherwise you'll need a WHERE Status IN ('fail', 'pass') condition additionally) - and the WITH ROLLUP will also give you the total number of rows for all statuses (with a Status = NULL in the output)

Upvotes: 2

Related Questions