• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 58
  • Last Modified:

Column Totals in Oracle Query

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 students
where enroll_status=0 
group by grade_level
order by grade_level

Open in new window

0
Basssque
Asked:
Basssque
  • 9
  • 9
  • 2
  • +2
2 Solutions
 
slightwv (䄆 Netminder) Commented:
Maybe this?
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
)

Open in new window

0
 
Pawan KumarDatabase ExpertCommented:
Please try below..

------

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

--

Open in new window


Enjoy !!
0
 
BasssqueAuthor Commented:
Slightvw,
Your example seems to add add a new column, I need the total per column at the bottom of each column.
0
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
BasssqueAuthor Commented:
Pawan Kumar Khowal,
Your example also adds a new column.  I need the total per column at the bottom of each column.
Thanks!
0
 
Pawan KumarDatabase ExpertCommented:
Ohhh in that case you can use ROLLUP, CUBE, GROUPING Functions.

Pls let me know if you need example on this.
0
 
BasssqueAuthor Commented:
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!
0
 
Pawan KumarDatabase ExpertCommented:
could you please post table information and some sample data. i shall do it.
0
 
BasssqueAuthor Commented:
I'm not sure how to provide sample data since we are counting records.  Is it possible to provide an example using the query that I originally posted?
0
 
Pawan KumarDatabase ExpertCommented:
Create table and insert data into table commands ?
0
 
BasssqueAuthor Commented:
Sorry I'm not familiar with those :-(
I'm only familiar with the read only queries as read only is the only rights I have...
0
 
Pawan KumarDatabase ExpertCommented:
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
)
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

--

Open in new window

0
 
BasssqueAuthor Commented:
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?
0
 
Pawan KumarDatabase ExpertCommented:
Something like this

--


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



--

Open in new window

0
 
Pawan KumarDatabase ExpertCommented:
You can change data. I just need the expected output.. If you will provide me I shall write the query for you.
0
 
BasssqueAuthor Commented:
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 students
where enroll_status=0 
group by rollup(grade_level)
order by grade_level

Open in new window

0
 
Pawan KumarDatabase ExpertCommented:
great ! Pls mark one answer as the accepted solution and close the question..
0
 
BasssqueAuthor Commented:
Thanks!!!!
0
 
awking00Commented:
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;
0
 
awking00Commented:
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.
0
 
Geert GruwezOracle dbaCommented:
Pivot also looks possible
0
 
Pawan KumarDatabase ExpertCommented:
@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,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
--

Open in new window

"

Anyways ! All the best..
0
 
BasssqueAuthor Commented:
just added rollup() to the group by clause of my original query
0

Featured Post

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

  • 9
  • 9
  • 2
  • +2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now