Count data using condition

Question

Post reply

Count data using condition

phina_el

SSC-Addicted

Points: 408
More actions
November 17, 2009 at 1:25 am

#135247

Dear All,
I have a problem to calculate all record and calculate one field which have two data different.
Example:
ID Status
------------
123 Active
234 Active
345 Inactive
567 Inactive
678 Inactive
how to display count record and calculating from field status?
Result:
Active Inactive Total
2 3 5
Thanks,

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 1

SELECT

SUM(CASE Status WHEN 'Active' THEN 1 ELSE 0 END) AS SumActive,

SUM(CASE Status WHEN 'Inactive' THEN 1 ELSE 0 END) AS SumInactive

Count(*) AS Total

FROM SomeTable

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

spaghettidba SSC Guru Points: 105732 More actions · Answer 2

ROLLUP is what you're looking for:

DECLARE @TempTab TABLE (

[ID] int,

[Status] varchar(10)

)

INSERT INTO @TempTab VALUES (123, 'Active')

INSERT INTO @TempTab VALUES (234, 'Active')

INSERT INTO @TempTab VALUES (345, 'Inactive')

INSERT INTO @TempTab VALUES (567, 'Inactive')

INSERT INTO @TempTab VALUES (678, 'Inactive')

SELECT [Status], COUNT(*)

FROM @TempTab

GROUP BY [Status]

WITH ROLLUP

If you were using SQL2008 you could use the new syntax ROLLUP([Status]).

Regards

Gianluca

-- Gianluca Sartori

spaghettidba SSC Guru Points: 105732 More actions · Answer 3

Now that I see Gail's reply I understand that you need it in one single row.

Another way to achieve it is with PIVOT:

SELECT [Active], [Inactive], [Total] = [Active] + [Inactive]

FROM @TempTab

PIVOT (COUNT(ID) FOR [Status] IN ([Active],[Inactive])) AS PVT

-- Gianluca Sartori

phina_el SSC-Addicted Points: 408 More actions · Answer 4

Thanks Gianluca,

Thanks Gail Shaw

I get the simple solution from you.

Regads,