The average business loses $13.5M per year to ineffective training (per 1,000 employees). Keep ahead of the competition and combine in-person quality with online cost and flexibility by training with Linux Academy.
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, school1, school2, school3, school4, school1+school2+school3+school4 school_total
from (
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
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
--
"If you are experiencing a similar issue, please ask a related question
Title | # Comments | Views | Activity |
---|---|---|---|
Access 2016 - query | 23 | 59 | |
SQL Help | 27 | 43 | |
sql server concatenate fields | 10 | 32 | |
T-SQL: Do I need CLUSTERED here? | 13 | 41 |
Join the community of 500,000 technology professionals and ask your questions.