count of true value for each boolean field

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

count of true value for each boolean field

Author

Topic

sanam
Starting Member

1 Post

Posted - 2008-09-02 : 00:54:03

hi
I have a table with three field they are Username,S1y,S1n that S1y,S1n are boolean field and save true and false values.

I want to count the number of S1y and S1n when they are true .for example if my table is this

username s1y s1n
user1 true true
user1 false true

it return 1 for s1y and 2 for s1n.

I have a stored proc for this .


PROCEDURE [dbo].[drug-ch1-report3]
(@username1 nvarchar(50)) AS
	select username,count(s1y) as s1y,count(s1n) as s1n
where (username=@username1)
group by username

this sp only count the number of record and return not the true value of them. then i changed sp to this sp :


PROCEDURE [dbo].[drug-ch1-report3]
(@username1 nvarchar(50)) AS
	select username,count(s1y) as s1y,count(s1n) as s1n
where (username=@username1)and(s1y='true') and (s1n='true')
group by username

this sp return the count of record that they have s1y and s1n true togheter. but i want the number of true value for each of them.
please help me.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-09-02 : 02:02:53

[code]CREATE PROCEDURE dbo.[drug-ch1-report3]
(
@username1 nvarchar(50)
)
AS

select username,
sum(case when s1y = 1 then 1 else 0 end) as s1y,
sum(case when s1n = 1 then 1 else 0 end) as s1n
where username = @username1
group by username[/code]

E 12°55'05.63"
N 56°04'39.26"