Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.
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