Using the query below, I need to add additional totals per column at the bottom of each column. I've got the totals by row but can't figure out how to add totals by column as well.
Can anyone point me in the right direction? I'm stuck on this one. Thanks!!

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 studentswhere enroll_status=0 group by grade_levelorder by grade_level

select grade_level, school1, school2, school3, school4, school1+school2+school3+school4 school_totalfrom (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) School4from studentswhere enroll_status=0 group by grade_levelorder 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_totalfrom studentswhere enroll_status=0 group by grade_levelorder by grade_level--

I do need an example if possible. I've tried rollup in the group by clause but it totally messed up the query results, probably because I wasn't using it properly. Thanks!

Can you try this. Now could you please give a exact output you need as per this data. If possible provide in a tabular format or may be in excel.(Attach)

--CREATE TABLE tabless( Grade_Level INT ,SCHOOLID SmallINT ,enroll_status TinyINT)GOINSERT INTO tablessVALUES(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)GOSELECT * FROM tablessselect 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) school4from tablessgroup by grade_levelWITH Cube--

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

I need to change the schoolid's on my end to different numbers. Do I change the numbers in the values section too? Sorry, this is the first time I've seen a query like that. The rollup command won't work in this case?

--SELECT grade_level,SchoolId,TotalSchoolIdsFROM (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--

Not sure how I messed it up the first time but I figured it out using my original query.

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 studentswhere enroll_status=0 group by rollup(grade_level)order by grade_level

compute sum of school1 on report
compute sum of school2 on report
compute sum of school3 on report
compute sum of school4 on report
compute sum of totals on report
break on report
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(*) totals
from students
where enroll_status=0
group by grade_level
order by grade_level;

I don't care about the points but you might want to take a look at my solution. There always seem to be more ways than one to reach a solution. For example, rather than the count(case... method, you could also use sum(decode, schoolid, 1, 1, 0) as school1, etc.

@Author - I have informed you about the grouping functions

"
Ohhh in that case you can use ROLLUP, CUBE, GROUPING Functions.

Pls let me know if you need example on this.

"

@Author - I have also given you the example of rollup...

"

--SELECT grade_level,SchoolId,TotalSchoolIdsFROM (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--

just added rollup() to the group by clause of my original query

0

Featured Post

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Confronted with some SQL you don't know can be a daunting task.
It can be even more daunting if that SQL carries some of the old secret codes used in the Ye Olde query syntax, such as:
(+) as used in Oracle; *= =* as used in Sybase …

Composite queries are used to retrieve the results from joining multiple queries after applying any filters. UNION, INTERSECT, MINUS, and UNION ALL are some of the operators used to get certain desired results.

This video explains at a high level about the four available data types in Oracle and how dates can be manipulated by the user to get data into and out of the database.

This video shows setup options and the basic steps and syntax for duplicating (cloning) a database from one instance to another. Examples are given for duplicating to the same machine and to different machines