The MCTS: Microsoft Exchange Server 2010 certification validates your skills in supporting the maintenance and administration of the Exchange servers in an enterprise environment. Learn everything you need to know with this course.
select grade_level,
COUNT(CASE WHEN SCHOOLID = '1' THEN 1 end) School1,
COUNT(CASE WHEN SCHOOLID = '2' THEN 1 end) School2,
COUNT(CASE WHEN SCHOOLID = '3' THEN 1 end) School3,
COUNT(CASE WHEN SCHOOLID = '4' THEN 1 end) School4,
COUNT(*) "Total by Grade"
from students
where enroll_status=0
group by grade_level
order by grade_level
------
select grade_level,
SUM(CASE WHEN SCHOOLID = '1' THEN 1 end) school1,
SUM(CASE WHEN SCHOOLID = '2' THEN 1 end) school2,
SUM(CASE WHEN SCHOOLID = '3' THEN 1 end) school3,
SUM(CASE WHEN SCHOOLID = '4' THEN 1 end) school4,
SUM(CASE WHEN SCHOOLID = '1' THEN 1 end) +
SUM(CASE WHEN SCHOOLID = '2' THEN 1 end) +
SUM(CASE WHEN SCHOOLID = '3' THEN 1 end) +
SUM(CASE WHEN SCHOOLID = '4' THEN 1 end) school_total
from students
where enroll_status=0
group by grade_level
order by grade_level
--
--
CREATE TABLE tabless
(
Grade_Level INT
,SCHOOLID SmallINT
,enroll_status TinyINT
)
GO
INSERT INTO tabless
VALUES
(1,1,0),
(1,2,0),
(1,3,0),
(1,4,0),
(1,2,0),
(1,3,0),
(1,1,0),
(1,1,0),
(1,1,0),
(1,2,0),
(1,3,0),
(1,4,1)
GO
SELECT * FROM tabless
select CASE WHEN grade_level IS NULL Then 'Total' ELSE CAST(grade_level AS sql_variant) END grade_level,
SUM(CASE WHEN SCHOOLID = '1' THEN 1 end) school1,
SUM(CASE WHEN SCHOOLID = '2' THEN 1 end) school2,
SUM(CASE WHEN SCHOOLID = '3' THEN 1 end) school3,
SUM(CASE WHEN SCHOOLID = '4' THEN 1 end) school4
from tabless
group by grade_level
WITH Cube
--
--
SELECT grade_level,SchoolId,TotalSchoolIds
FROM (
SELECT CASE WHEN grade_level IS NULL Then 'Total' ELSE CAST(grade_level AS sql_variant) END grade_level, SCHOOLID SchoolId
,SUM(SCHOOLID) AS TotalSchoolIds FROM tabless
WHERE enroll_status=0
GROUP BY ROLLUP(grade_level,SCHOOLID) ) t
--
select grade_level,
COUNT(CASE WHEN SCHOOLID = '1' THEN 1 end) School1,
COUNT(CASE WHEN SCHOOLID = '2' THEN 1 end) School2,
COUNT(CASE WHEN SCHOOLID = '3' THEN 1 end) School3,
COUNT(CASE WHEN SCHOOLID = '4' THEN 1 end) School4,
COUNT(*) "Total by Grade"
from students
where enroll_status=0
group by rollup(grade_level)
order by grade_level
--
SELECT grade_level,SchoolId,TotalSchoolIds
FROM (
SELECT CASE WHEN grade_level IS NULL Then 'Total' ELSE CAST(grade_level AS sql_variant) END grade_level, SCHOOLID SchoolId
,SUM(SCHOOLID) AS TotalSchoolIds FROM tabless
WHERE enroll_status=0
GROUP BY ROLLUP(grade_level,SCHOOLID) ) t
--
"Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.
Have a better answer? Share it in a comment.
Open in new window