SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 how to count number of yes and no in the same tabl
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

sayer
Starting Member

Saudi Arabia
35 Posts

Posted - 04/16/2013 :  14:57:31  Show Profile  Reply with Quote
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

USA
1974 Posts

Posted - 04/16/2013 :  15:52:33  Show Profile  Visit chadmat's Homepage  Reply with Quote
Is it possible to have 0 yes or no states for a given student id?

-Chad
Go to Top of Page

sayer
Starting Member

Saudi Arabia
35 Posts

Posted - 04/16/2013 :  15:58:42  Show Profile  Reply with Quote
how to write select to do that ?

http://aman-services.net
for office
???? ???? ???????
Go to Top of Page

chadmat
The Chadinator

USA
1974 Posts

Posted - 04/16/2013 :  16:06:10  Show Profile  Visit chadmat's Homepage  Reply with Quote
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
Go to Top of Page

sayer
Starting Member

Saudi Arabia
35 Posts

Posted - 04/16/2013 :  16:16:52  Show Profile  Reply with Quote
i want get report about absent and present in lecture

http://aman-services.net
for office
???? ???? ???????
Go to Top of Page

chadmat
The Chadinator

USA
1974 Posts

Posted - 04/16/2013 :  16:38:06  Show Profile  Visit chadmat's Homepage  Reply with Quote
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
Go to Top of Page

Lamprey
Flowing Fount of Yak Knowledge

4614 Posts

Posted - 04/16/2013 :  18:07:45  Show Profile  Reply with Quote
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
Go to Top of Page

djj55
Constraint Violating Yak Guru

USA
317 Posts

Posted - 04/17/2013 :  13:25:43  Show Profile  Reply with Quote
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
Go to Top of Page

ScottPletcher
Constraint Violating Yak Guru

USA
362 Posts

Posted - 04/18/2013 :  15:35:22  Show Profile  Reply with Quote

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
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.08 seconds. Powered By: Snitz Forums 2000