Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Column Totals in Oracle Query

Posted on 2016-09-26
22
Medium Priority
?
56 Views
Last Modified: 2016-10-01
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
Comment
Question by:Basssque
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 9
  • 9
  • 2
  • +2
22 Comments
 
LVL 77

Expert Comment

by:slightwv (䄆 Netminder)
ID: 41816261
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
 
LVL 32

Expert Comment

by:Pawan Kumar
ID: 41816272
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
 

Author Comment

by:Basssque
ID: 41816278
Slightvw,
Your example seems to add add a new column, I need the total per column at the bottom of each column.
0
Veeam Task Manager for Hyper-V

Task Manager for Hyper-V provides critical information that allows you to monitor Hyper-V performance by displaying real-time views of CPU and memory at the individual VM-level, so you can quickly identify which VMs are using host resources.

 

Author Comment

by:Basssque
ID: 41816290
Pawan Kumar Khowal,
Your example also adds a new column.  I need the total per column at the bottom of each column.
Thanks!
0
 
LVL 32

Assisted Solution

by:Pawan Kumar
Pawan Kumar earned 2000 total points
ID: 41816291
Ohhh in that case you can use ROLLUP, CUBE, GROUPING Functions.

Pls let me know if you need example on this.
0
 

Author Comment

by:Basssque
ID: 41816295
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
 
LVL 32

Expert Comment

by:Pawan Kumar
ID: 41816298
could you please post table information and some sample data. i shall do it.
0
 

Author Comment

by:Basssque
ID: 41816311
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
 
LVL 32

Expert Comment

by:Pawan Kumar
ID: 41816317
Create table and insert data into table commands ?
0
 

Author Comment

by:Basssque
ID: 41816322
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
 
LVL 32

Expert Comment

by:Pawan Kumar
ID: 41816331
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
 

Author Comment

by:Basssque
ID: 41816344
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
 
LVL 32

Expert Comment

by:Pawan Kumar
ID: 41816352
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
 
LVL 32

Expert Comment

by:Pawan Kumar
ID: 41816355
You can change data. I just need the expected output.. If you will provide me I shall write the query for you.
0
 

Accepted Solution

by:
Basssque earned 0 total points
ID: 41816357
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
 
LVL 32

Expert Comment

by:Pawan Kumar
ID: 41816363
great ! Pls mark one answer as the accepted solution and close the question..
0
 

Author Comment

by:Basssque
ID: 41816365
Thanks!!!!
0
 
LVL 32

Expert Comment

by:awking00
ID: 41816407
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
 
LVL 32

Expert Comment

by:awking00
ID: 41816422
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
 
LVL 38

Expert Comment

by:Geert Gruwez
ID: 41816875
Pivot also looks possible
0
 
LVL 32

Expert Comment

by:Pawan Kumar
ID: 41817050
@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
 

Author Closing Comment

by:Basssque
ID: 41824651
just added rollup() to the group by clause of my original query
0

Featured Post

Get free NFR key for Veeam Availability Suite 9.5

Veeam is happy to provide a free NFR license (1 year, 2 sockets) to all certified IT Pros. The license allows for the non-production use of Veeam Availability Suite v9.5 in your home lab, without any feature limitations. It works for both VMware and Hyper-V environments

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
Shell script to create broker configuration file using current broker Configuration, solely for purpose of backup on Linux. Script may need to be modified depending on OS-installation. Please deploy and verify the script in a test environment.
This video shows information on the Oracle Data Dictionary, starting with the Oracle documentation, explaining the different types of Data Dictionary views available by group and permissions as well as giving examples on how to retrieve data from th…
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

618 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question