Issue related to Counting Record on Group by clause

I have one database table having StudentId(int),Subject(varchar(50)),Marks(int),IsPass(int),ExamDate(Datetime)

Table can have more than one record for same Subject for particular student for different Date.

I wrote following query :

开发者_高级运维

select StudentId, Count(IsPass) 
from ExamEntry
where IsPass =1  group by StudentId

but dont want Where condition in the Query:

it is Possible something like this :

Select StudentId, case when IsPass = 1 then count(IsPass) end 
from ExamEntry 
group by studentId

but it display more that one record for perticular studentId

How can i achieve my goal ?

---

The answer is so simple nobody saw it :-)

SELECT StudentId, SUM(IsPass) 
FROM ExamEntry GROUP BY StudentId

---

SELECT StudentId,
       COUNT(CASE WHEN IsPass = 1 THEN 'X' END) AS NumberOfPasses
FROM   ExamEntry
GROUP  BY StudentId  

COUNT only counts NOT NULL values and CASE has an implicit ELSE NULL

---

Perhaps You need something like this?

SELECT StudentId, SUM(CASE WHEN IsPass = 1 THEN 1 ELSE 0 END) AS Number_Of_Passed_Exams
FROM ExamEntry
GROUP BY StudentId

---

If you don't want to use where clause for it than Having clause is its simplest alternate.

Try this out.

select StudentId, count(IsPass)AS PassedCount
from ExamEntry
group by StudentId,IsPass
having IsPass= 1