Column Count in SQL - SQL Server Forums

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)

Column Count in SQL

Author

Topic

greatbear302
Starting Member

13 Posts

Posted - 2009-05-24 : 13:30:57

Hello,

I'm an average user of SQL, but have ran into a problem which seems to be quite simple but because of my lack of basics, I'm unable to solve it.

There is a table of grades for students. (for simplicity)There are 3 columns for courses, and 1 column for StudentID. A student can get either Pass(P) or Fail(F) per grade.

So the Grade table data looks like this:

StudentID English Maths Science
--------------------------------
001 P P F
002 P F F
003 F P P
004 F F F
005 P P P

I want to count the number of Pass per student. The resultset should be something like this:

StudentID TotalPass
-------------------
001 2
002 1
003 2
004 0
005 3

StudentID is int, Grades are nchar(1).

Any help greatly appreciated!

nr
SQLTeam MVY

12543 Posts

Posted - 2009-05-24 : 13:41:48

select StudentID ,
TotalPass = len(replace(english + Maths + science,'F',''))
from tbl

or

select StudentID ,
TotalPass = case when english = 'P' then 1 else 0 end + case when Maths = 'P' then 1 else 0 end + case when science = 'P' then 1 else 0 end
from tbl

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-05-24 : 14:06:19

SELECT u.StudentID, SUM(CASE WHEN u.theValue = 'P' THEN 1 ELSE 0 END) AS Pass
FROM Grade AS g
UNPIVOT (theValue for theCol IN (g.English, g.Maths, g.Science) AS u
GROUP BY u.StudentID

E 12°55'05.63"
N 56°04'39.26"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-05-24 : 14:11:48

SELECT u.StudentID, COUNT(u.theValue) AS Pass
FROM Grade AS g
UNPIVOT (theValue for theCol IN (g.English, g.Maths, g.Science) AS u
WHERE u.theValue = 'P'
GROUP BY ALL u.StudentID

E 12°55'05.63"
N 56°04'39.26"

greatbear302
Starting Member

13 Posts

Posted - 2009-05-24 : 15:31:17

quote:
Originally posted by Peso

SELECT u.StudentID, COUNT(u.theValue) AS Pass
FROM Grade AS g
UNPIVOT (theValue for theCol IN (g.English, g.Maths, g.Science)) AS u
WHERE u.theValue = 'P'
GROUP BY ALL u.StudentID

E 12°55'05.63"
N 56°04'39.26"

Thank you so much Peso. This worked like magic. And thanks again for the prompt reply!

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-05-24 : 18:04:40

Thank you.
You should try out the suggestions made by nr too.

E 12°55'05.63"
N 56°04'39.26"

greatbear302
Starting Member

13 Posts

Posted - 2009-05-24 : 21:04:08

quote:
Originally posted by Peso

Thank you.
You should try out the suggestions made by nr too.

E 12°55'05.63"
N 56°04'39.26"

I just tried nr's suggestion. That was smart. Its really great how skill gets passed down through these forums.

greatbear302
Starting Member

13 Posts

Posted - 2009-05-28 : 17:23:34

quote:
Originally posted by Peso

SELECT u.StudentID, COUNT(u.theValue) AS Pass
FROM Grade AS g
UNPIVOT (theValue for theCol IN (g.English, g.Maths, g.Science) AS u
WHERE u.theValue = 'P'
GROUP BY ALL u.StudentID

E 12°55'05.63"
N 56°04'39.26"

What if I want to count
total English grades that received a P and
total Maths grades that received a P and
total Science grades that received a P, in one query?

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-05-28 : 17:31:54

[code]SELECT u.theCol, COUNT(u.theValue) AS Pass
FROM Grade AS g
UNPIVOT (theValue for theCol IN (g.English, g.Maths, g.Science) AS u
WHERE u.theValue = 'P'
GROUP BY ALL u.theCol[/code]

E 12°55'05.63"
N 56°04'39.26"

greatbear302
Starting Member

13 Posts

Posted - 2009-05-28 : 17:53:04

quote:

Originally posted by Peso

SELECT u.theCol, COUNT(u.theValue) AS Pass
FROM Grade AS g
UNPIVOT (theValue for theCol IN (g.English, g.Maths, g.Science) AS u
WHERE u.theValue = 'P'
GROUP BY ALL u.theCol

E 12°55'05.63"
N 56°04'39.26"

You Rock Peso!!

I want to join another table with Grade, but not sure where to include the join statement.

If I include it after the unpivot, I get "multi-part identifier...cannot be bound" on the right side of "ON" clause. If I include it before the unpivot, I get "multi-part identifier...cannot be bound" on other expressions...and other errors.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-05-28 : 17:56:21

Well... All I did this time was replacing the "u.StudentID" column with "u.theCol" column.

Anyways, thank you.

E 12°55'05.63"
N 56°04'39.26"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-05-28 : 18:07:33

[code]select x.theCol, x.theValue
from (
SELECT u.theCol, COUNT(u.theValue) AS Pass
FROM Grade AS g
UNPIVOT (theValue for theCol IN (g.English, g.Maths, g.Science) AS u
WHERE u.theValue = 'P'
GROUP BY ALL u.theCol
) as x
inner join tbl1 as t1 on t1.somecol = x.thecol[/code]

E 12°55'05.63"
N 56°04'39.26"

greatbear302
Starting Member

13 Posts

Posted - 2009-05-28 : 19:59:58

Alright...now its getting even more complex. Let's say there is a teacher's table. Each teacher has a set of student's assigned to him/er, identified by StudentID. I want the total number of P's a teacher got among all his/er students.

Teacher's table might look like:
TeacherID TeacherName
----------------------
101 Mr. X
102 Mr. Y
103 Ms. Z

and a Student's table might look like
StudentID StudentName TeacherID
-------------------------------
201 Kid_A Mr. X
202 Kid_B Mr. Y
203 Kid_C Mr. X
204 Kid_D Ms. Z
205 Kid_E Ms. Z
206 Kid_F Ms. Z
207 Kid_G Mr. Y
208 Kid_H Mr. Y

As you see, teacher Mr. X has 2 kids, Mr. Y has 3 kids and Ms. Z has 3 kids.

And the grades table looks like this
StudentID English Maths Science
--------------------------------
201 P P F
202 P F F
203 F P P
204 F F F
205 P P P
206 P F P
207 F F P
208 P P P

I want a resultset which looks this (total number of P's a teacher got among all his/er students):
TeacherID TeacherName TotalEnglishP TotalMathsP TotalScienceP
---------------------------------------------------
101 Mr. X 1 2 1
102 Mr. Y 2 1 2
103 Ms. Z 2 1 2

bklr
Master Smack Fu Yak Hacker

1693 Posts

Posted - 2009-05-29 : 01:15:39

try this one


SELECT 
teacherid,teachername,
SUM(CASE WHEN subjects ='english' and marks = 'p' THEN 1 ELSE 0 END) totalenglishcnt ,
SUM(CASE WHEN subjects ='maths' and marks = 'p' THEN 1 ELSE 0 END) totalmathscnt ,
SUM(CASE WHEN subjects ='science' and marks = 'p' THEN 1 ELSE 0 END) totalsciencecnt 
FROM (
	SELECT teacherid,teachername,subjects,marks 
	FROM (
		SELECT t.teacherid,t.teachername,
				s.studentid,english,maths,science 
		FROM teachers t
		INNER JOIN students s ON s.teacherid = t.teachername
		INNER JOIN Grades r ON r.studentid = s.studentid 
		)s
	UNPIVOT (marks FOR subjects in (english,maths,science))p
	)k
GROUP BY teacherid,teachername

greatbear302
Starting Member

13 Posts

Posted - 2009-05-29 : 09:14:40

Thanks bklr. It looks like your query will work. My friend had a similar solution, with individual SUM clause for each grade, and it works.

I was hoping to eliminate the individual SUM's. The actual problem has about 30 grade columns. I'l keep working on it, but if I can get the desired solution from here that would be great too!

greatbear302
Starting Member

13 Posts

Posted - 2009-05-29 : 18:56:23

Just found out about functions in SQL Server(Im using 2008 express edition). The scalar-valued functions solve a lot of problems. Almost like get your resultset as in high level programming!

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-05-30 : 00:39:29

But with a performance hit, if you're not careful HOW you write your function.

E 12°55'05.63"
N 56°04'39.26"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources