Shan k Shan k - 6 months ago 9
SQL Question

Count in s Table SQL Server

I like to create a simple query to count number of similar values in a column. Example is here below

declare @temo table (sno int identity, dyscode varchar(200), isVerified bit, isCorrect bit, isRejected bit, isDeleted bit)

insert into @temo values('dys',1,1,0,0)
insert into @temo values('dys',1,1,0,0)
insert into @temo values('dys',1,0,1,0)
insert into @temo values('dys',1,0,0,1)
insert into @temo values('dys',1,0,1,0)
insert into @temo values('dys',1,0,0,1)
insert into @temo values('dys',0,0,0,0)
insert into @temo values('dys',1,0,0,1)


Expected Output Should be


'dys',7,2,3


Please share some quick and efficient solutions for the same. This operations needs to be run on millions of records, so performance will be a great concern.

Answer

You needs to cast the bit columns to do sum function on them because sum function does not support bit data type.

Link for Reference : https://msdn.microsoft.com/en-IN/library/ms187810.aspx

Below query will the required result :-

SELECT   dyscode,
     sum(cast(isVerified as int)),
     sum(cast(isCorrect as int)),
     sum(cast(isRejected as int)),
     sum(cast(isDeleted as int))
FROM     @temo
GROUP BY dyscode
Comments