Assistance with Count - 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 2012 Forums

Transact-SQL (2012)

Assistance with Count

Author

Topic

sdo3lg
Starting Member

1 Post

Posted - 2015-03-15 : 09:45:49

I currently have the following SQL:

SELECT
(SELECT DISTINCT CourseN FROM tb_course WHERE CourseN = 'CH334-' AND Sect = 'M1' AND year = 2014 AND Semester = 'FA' AND Proft = 25) AS [Course],
(SELECT DISTINCT Sect FROM tb_course WHERE CourseN = 'CH334-' AND Sect = 'M1' AND year = 2014 AND Semester = 'FA' AND Proft = 25) AS [Section],
(SELECT DISTINCT Skill2 FROM tb_course WHERE CourseN = 'CH334-' AND Sect = 'M1' AND year = 2014 AND Semester = 'FA' AND ProfT = 25) AS [Skill],
(SELECT COUNT(Skill2CA) FROM tb_course WHERE CourseN = 'CH334-' AND Sect = 'M1' AND year = 2014 AND Semester = 'FA' AND ProfT = 25 AND Skill2CA in ('S','F')) AS [Complete],
(SELECT COUNT(Skill2CA) FROM tb_course WHERE CourseN = 'CH334-' AND Sect = 'M1' AND year = 2014 AND Semester = 'FA' AND ProfT = 25) AS [Total]

Which has an output of:

Course Section Skill Complete Total
CH334- M1 W 17 19

What can I do to get it to where the CourseN in the WHERE clause is unknown? I have tried placing initially in a table but the output is wrong on acomp. I would rather not have to use a temp table if possible.

UPDATE tb_temp_mycourses
SET acomp = ( SELECT COUNT(Skill2CA)
FROM tb_course
INNER JOIN tb_temp_mycourses ON tb_course.CourseN = tb_temp_mycourses.course
WHERE tb_course.CourseN = tb_temp_mycourses.course
AND tb_course.Sect = tb_temp_mycourses.section
AND tb_course.Skill1 = tb_temp_mycourses.skill
AND year = 2014
AND Semester = 'FA'
AND ProfT = 25
AND Skill2CA in ('S','F'))

Output:
id prof course section skill acomp tcomp pcomp
81 25 CH230- M1 Q 17 NULL NULL
82 25 CH334- M1 Q 17 NULL NULL
83 25 CH334- M1 W 17 NULL NULL

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2015-03-15 : 15:19:44

first rewrite your query:


select CourseN, Sect, Skill2CA,
   Count(CASE When Skill2CA in ('S','F') then 1 END) as Complete
   Count(Skill2CA) as Total
From tb_course
WHERE CourseN = 'CH334-' AND Sect = 'M1' AND year = 2014 AND Semester = 'FA' AND Proft = 25
group by CourseN, Sect, Skill2CA

Then, you only have to change the first predicate in the WHERE clause to return CourseN = 'Unknown'

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources