New to SQL Trying to combine 2 statements

Question

New to SQL Trying to combine 2 statements

pkaraffa

SSC Enthusiast

Points: 135
More actions
December 21, 2010 at 1:22 pm

#229727

Good afternoon,
I am new to SQL what I am trying to do is to combine these 2 statements into 1. Can anyone please help me! Thank you in advance!
SELECT SCRID,COUNT(CASE WHEN present = 1 THEN 1 END)Present
FROM dbo.attendance
select dbo.Student.SID, dbo.Student.LastName, dbo.Student.FirstName,dbo.StudentClassroom.SCRID
from dbo.Student, dbo.StudentClassroom
where dbo.Student.SID=dbo.StudentClassroom.SID

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

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

parthi-1705 SSCrazy Eights Points: 9277 More actions · Answer 1

pkaraffa (12/21/2010)
Good afternoon,
I am new to SQL what I am trying to do is to combine these 2 statements into 1. Can anyone please help me! Thank you in advance!
SELECT SCRID,COUNT(CASE WHEN present = 1 THEN 1 END)Present
FROM dbo.attendance
select dbo.Student.SID, dbo.Student.LastName, dbo.Student.FirstName,dbo.StudentClassroom.SCRID
from dbo.Student, dbo.StudentClassroom
where dbo.Student.SID=dbo.StudentClassroom.SID

Hi

Is this you are looking for

Select

Student.SID,

Student.LastName,

Student.FirstName,

StudentClassroom.SCRID,

Case When attendance.present = 1 THEN 1 Else 0 END as Present

from Student

join

StudentClassroom

On Student.SID=StudentClassroom.SID

Join attendance

on attendance.SCRID=StudentClassroom.SCRID

Thanks

Parthi

Thanks
Parthi

pkaraffa SSC Enthusiast Points: 135 More actions · Answer 2

Thanks you so much for responding. That is close but the attendace table has true and false in which when I run the first statement it will give me something like

SCRIDPresent

155966

125771

158927

57020

When I run the code above it gives me 58595 rows of data. My main table has only 996 entries. Trying to get something like below.

LastNameFirstNameSCRIDSum of the attendance

xxxxxxxxxxxx155966

xxxxxxxxxxxx125771

xxxxxxxxxxxx158927

xxxxxxxxxxxx 57020

xxxxxxxxxxxx154060

Thank you so much again for looking at this problem.

sturner SSC-Insane Points: 22411 More actions · Answer 3

Parthi had it pretty close:

try this:

Select

Student.LastName,

Student.FirstName,

StudentClassroom.SCRID,

sum(Case When attendance.present = 1 THEN 1 Else 0 END) as Attendance

from Student

join

StudentClassroom

On Student.SID=StudentClassroom.SID

Join attendance

on attendance.SCRID=StudentClassroom.SCRID

group by Student.LastName,

Student.FirstName,

StudentClassroom.SCRID

The probability of survival is inversely proportional to the angle of arrival.

pkaraffa SSC Enthusiast Points: 135 More actions · Answer 4

Old Hand

That is it thank you both so much! I appreciate all of the help and guidance that you have given me!