Count Problem Urgent

[nish85]

Hi

I have a table named StudentResult









UserID School Semester Grade Campus Status

1 School1 Summer 10 Campus1 Accepted
2 School1 Summer 10 Campus1 Accepted
3 School1 Summer 10 Campus1 Rejected
4 School1 Summer 10 Campus1 Rejected
5 School1 FALL 10 Campus1 Accepted
6 School1 Summer 12 Campus1 Accepted
7 School1 Summer 12 Campus1 Rejected
8 School1 Summer 12 Campus1 Accepted
9 School1 Summer 12 Campus1 Rejected
10 School1 Summer 12 Campus1 Accepted
11 School1 FALL 12 Campus1 Rejected
12 School1 FALL 12 Campus1 Accepted
13 School1 FALL 11 Campus1 Rejected
14 School1 FALL 11 Campus1 Accepted
15 School1 FALL 11 Campus1 Accepted
16 School1 Summer 11 Campus1 Rejected
17 School1 Summer 11 Campus1 Accepted
18 School1 Summer 11 Campus1 Accepted


I want to take the number of accepted and rejected users from the above table as the result table below.I want to consider grade,Campus and semester for taking count and the result should be like result table.Please help me to write Query for this.
I am using SQL server2000. as database


School Semester Grade Campus NoofAccepted NoofRejected

School1 Summer 10 Campus1 2 2
School1 Fall 10 Campus1 1 0
School1 Summer 11 Campus1 2 1
School1 Fall 11 Campus1 2 1
School1 Summer 12 Campus1 3 2
School1 Fall 12 Campus1 1 1

Thanx

[clear1140]

Hi

I have a table named StudentResult









UserID School Semester Grade Campus Status

1 School1 Summer 10 Campus1 Accepted
2 School1 Summer 10 Campus1 Accepted
3 School1 Summer 10 Campus1 Rejected
4 School1 Summer 10 Campus1 Rejected
5 School1 FALL 10 Campus1 Accepted
6 School1 Summer 12 Campus1 Accepted
7 School1 Summer 12 Campus1 Rejected
8 School1 Summer 12 Campus1 Accepted
9 School1 Summer 12 Campus1 Rejected
10 School1 Summer 12 Campus1 Accepted
11 School1 FALL 12 Campus1 Rejected
12 School1 FALL 12 Campus1 Accepted
13 School1 FALL 11 Campus1 Rejected
14 School1 FALL 11 Campus1 Accepted
15 School1 FALL 11 Campus1 Accepted
16 School1 Summer 11 Campus1 Rejected
17 School1 Summer 11 Campus1 Accepted
18 School1 Summer 11 Campus1 Accepted


I want to take the number of accepted and rejected users from the above table as the result table below.I want to consider grade,Campus and semester for taking count and the result should be like result table.Please help me to write Query for this.
I am using SQL server2000. as database


School Semester Grade Campus NoofAccepted NoofRejected

School1 Summer 10 Campus1 2 2
School1 Fall 10 Campus1 1 0
School1 Summer 11 Campus1 2 1
School1 Fall 11 Campus1 2 1
School1 Summer 12 Campus1 3 2
School1 Fall 12 Campus1 1 1

Thanx

I hope this could help

SELECT a.School, a.Semester, a.Grade, a.Campus,
(SELECT COUNT(Status)FROM StudentResult WHERE School = a.School AND Semester = a.Semester AND Grade = a.Grade AND Campus = a.Campus AND Status = 'Accepted')
AS NoofAccepted,
(SELECT COUNT(Status)FROM StudentResult WHERE School = a.School AND Semester = a.Semester AND Grade = a.Grade AND Campus = a.Campus AND Status = 'Rejected')
AS NoofRejected
FROM StudentResult a
group by a.School, a.Semester, a.Grade, a.Campus
order by a.School, a.Semester, a.Grade, a.Campus

[sayedul]

Hi

I have a table named StudentResult









UserID School Semester Grade Campus Status

1 School1 Summer 10 Campus1 Accepted
2 School1 Summer 10 Campus1 Accepted
3 School1 Summer 10 Campus1 Rejected
4 School1 Summer 10 Campus1 Rejected
5 School1 FALL 10 Campus1 Accepted
6 School1 Summer 12 Campus1 Accepted
7 School1 Summer 12 Campus1 Rejected
8 School1 Summer 12 Campus1 Accepted
9 School1 Summer 12 Campus1 Rejected
10 School1 Summer 12 Campus1 Accepted
11 School1 FALL 12 Campus1 Rejected
12 School1 FALL 12 Campus1 Accepted
13 School1 FALL 11 Campus1 Rejected
14 School1 FALL 11 Campus1 Accepted
15 School1 FALL 11 Campus1 Accepted
16 School1 Summer 11 Campus1 Rejected
17 School1 Summer 11 Campus1 Accepted
18 School1 Summer 11 Campus1 Accepted


I want to take the number of accepted and rejected users from the above table as the result table below.I want to consider grade,Campus and semester for taking count and the result should be like result table.Please help me to write Query for this.
I am using SQL server2000. as database


School Semester Grade Campus NoofAccepted NoofRejected

School1 Summer 10 Campus1 2 2
School1 Fall 10 Campus1 1 0
School1 Summer 11 Campus1 2 1
School1 Fall 11 Campus1 2 1
School1 Summer 12 Campus1 3 2
School1 Fall 12 Campus1 1 1

Thanx

Hi,

Try this below:

select School, Semester, Grade, Campus,
NoofAccepted = sum(case Status when 'Accepted' then 1 else 0 end),
NoofRejected = sum(case Status when 'Rejected' then 1 else 0 end)
from StudentResult
group by School, Semester, Grade, Campus

This is tested now and will work fine.