how to count number of yes and no in the same tabl

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

SQL Server 2008 Forums

Transact-SQL (2008)

how to count number of yes and no in the same tabl

Author

Topic

sayer
Starting Member

35 Posts

Posted - 2013-04-16 : 14:57:31

tabel

|------id_student-----|--id_lecture------|--state------|
29111 1 yes
29111 2 yes
29111 3 yes
29111 4 no
29111 5 no

29222 1 yes
29222 2 no
29222 3 yes
29222 4 no
29222 5 no
------------------------------------------------------
i select number of state yes and no group by student id
|------id_student-----|--number_of_yes------|--number_of_no------|
29111 3 2
29222 2 3

how to do that ?
please help me

http://aman-services.net
for office
???? ???? ???????

chadmat
The Chadinator

1974 Posts

Posted - 2013-04-16 : 15:52:33

Is it possible to have 0 yes or no states for a given student id?

-Chad

sayer
Starting Member

35 Posts

Posted - 2013-04-16 : 15:58:42

how to write select to do that ?

http://aman-services.net
for office
???? ???? ???????

chadmat
The Chadinator

1974 Posts

Posted - 2013-04-16 : 16:06:10

Is it possible for a student id to have a yes entry and not any no entries, or have a no entry, with 0 yes entries?

-Chad

sayer
Starting Member

35 Posts

Posted - 2013-04-16 : 16:16:52

i want get report about absent and present in lecture

http://aman-services.net
for office
???? ???? ???????

chadmat
The Chadinator

1974 Posts

Posted - 2013-04-16 : 16:38:06

Alright,

This should work unless You can have "no" entries without a "yes" entry, but since you won't tell me if that is a valid case, I won't waste time solving that problem:

create table #t1(id int, state varchar(3))
go
insert into #t1 values (1, 'yes')
insert into #t1 values (1, 'no')
insert into #t1 values (1, 'yes')
insert into #t1 values (1, 'yes')
insert into #t1 values (2, 'yes')
insert into #t1 values (2, 'yes')
insert into #t1 values (2, 'yes')
insert into #t1 values (3, 'yes')
insert into #t1 values (3, 'no')
insert into #t1 values (3, 'no')
go

select isnull(#t1.id, a.id) id, isnull(COUNT(1), 0) Yes, isnull(no, 0) No
from #t1 left join (select id, COUNT(1) no
from #t1
where state = 'no'
group by id) a
on #t1.id = a.id
where #t1.state='yes'
group by #t1.id, a.id, no
go

drop table #t1

-Chad

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2013-04-16 : 18:07:45

Slightly more simplified:

SELECT 
	ID AS id_student, 
	SUM(CASE WHEN state = 'yes' THEN 1 ELSE 0 END) AS number_of_yes,
	SUM(CASE WHEN state = 'no' THEN 1 ELSE 0 END) AS number_of_no
FROM 
	#t1
GROUP BY 
	ID

djj55
Constraint Violating Yak Guru

352 Posts

Posted - 2013-04-17 : 13:25:43

Lamprey has a good answer which will show zero total.
Here is something to think about.

DECLARE @t1 TABLE (id int, state varchar(3))
insert into @t1 values (1, 'yes')
insert into @t1 values (1, 'no')
insert into @t1 values (1, 'yes')
insert into @t1 values (1, 'yes')
insert into @t1 values (2, 'yes')
insert into @t1 values (2, 'yes')
insert into @t1 values (2, 'yes')
insert into @t1 values (3, 'yes')
insert into @t1 values (3, 'no')
insert into @t1 values (3, 'no')

select DISTINCT 
    ID, 
    state,
    COUNT(ID) OVER(PARTITION BY id, state) AS 'Count'
from @t1

djj

ScottPletcher
Aged Yak Warrior

550 Posts

Posted - 2013-04-18 : 15:35:22

[code]
SELECT
id_student,
SUM(CASE WHEN state = 'yes' THEN 1 ELSE 0 END) AS number_of_yes,
SUM(CASE WHEN state = 'no' THEN 1 ELSE 0 END) AS number_of_no
FROM dbo.tablename
GROUP BY id_student
ORDER BY id_student --optional, of course
[/code]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources