Running Totals and SubTotals 4

[VBRookie]

Hi, I'm trying to write a query that will return to me running totals and subtotals. I have the running totals working ok but the subtotals are giving me a problem.

Below is an illustration of what I mean:

project  proj hours  employee  emp hours  emp totals
-------  ----------  --------  ---------  ----------
skyler   10          john      2          2
skyler   10          becky     4          8
skyler   10          becky     4          8
quasar   3           alissa    1          1.5
quasar   3           alissa    .5         1.5
quasar   3           brian     1.5        1.5

Does this make sense?

The running proj hours works fine but for the the employee total hours it doesn't reset for each employee so it turns out to be a running total as well.

Does anyone know what I'm trying to do? I don't know if I've been as clear as I need to be. Any helpful feedback would be greatly appreciated.

VB Rookie

 

[SirCharles]

When stuff gets complicated where you have to mess with multiple totals, it's usually nice and faster and more easily changed with PL/SQL rather than straigth sql.

Would you consider a simple PL/SQL program for this type of report? Or Oracle report writer?

 

[SantaMufasa]

VBRookie,

As SirCharles implied, doing what you want, in Oracle SQL, requires the use of PL/SQL modules. Below, I post everything I did to produce the results you want:

===================================================
1) Load data:
SQL> create table VBRookie
  2   (project varchar2(30)
  3   ,employee varchar2(20)
  4   ,emp_hours number);

Table created.

SQL> insert into VBRookie values ('skyler','becky',4);

1 row created.

SQL> insert into VBRookie values ('skyler','john',2);

1 row created.

SQL> insert into VBRookie values ('skyler','becky',4);

1 row created.

SQL> insert into VBRookie values ('quasar','alissa',1.5);

1 row created.

SQL> insert into VBRookie values ('quasar','brian',1.5);

1 row created.

SQL> insert into VBRookie values ('quasar','alissa',1);

1 row created.
===================================================
2) Create necessary PL/SQL modules:
create or replace package VBRookie_pkg is
	accum_hours		number;
	curr_proj		varchar2(200);
	curr_employee		varchar2(200);
	function proj_hours (proj_in in varchar2) return number;
	function emp_totals	(proj_in in varchar2
				,emp_in in varchar2
				,emp_hours in number) return number;
	pragma restrict_references(proj_hours,WNDS);
	pragma restrict_references(emp_totals,WNDS);
end;
/

Package created.

create or replace package body VBRookie_pkg is
	function proj_hours (proj_in in varchar2) return number is
		proj_total	number;
	begin
		select sum(emp_hours) into proj_total
			from vbrookie
			where project = proj_in;
		return proj_total;
	end;
	function emp_totals 	(proj_in in varchar2
				,emp_in in varchar2
				,emp_hours in number) return number is
		aud_str	varchar2(1000);
	begin
		if curr_proj = proj_in and curr_employee = emp_in then
			null;
		else
			accum_hours := 0;
			curr_proj := proj_in;
			curr_employee := emp_in;
		end if;
		accum_hours	:= accum_hours + emp_hours;
		return accum_hours;
	end;
end;
/

Package body created.

===================================================

3) Access the table, using above functions, to produce the results you want:

SQL> col a heading "project" format a7
SQL> col b heading "proj|hours" format 999.9
SQL> col c heading "employee" format a8
SQL> col d heading "emp|hours" format 999.9
SQL> col e heading "emp|totals" format 999.9
SQL> select project a, VBRookie_pkg.proj_hours(project) b, employee c, emp_hours d, 
  2    VBRookie_pkg.emp_totals(project, employee, emp_hours) e
  3   from (select * from VBRookie
  4   order by project, employee, emp_hours) VB
  5  /

          proj             emp    emp
project  hours employee  hours totals
------- ------ -------- ------ ------
quasar     4.0 alissa      1.0    1.0
quasar     4.0 alissa      1.5    2.5
quasar     4.0 brian       1.5    1.5
skyler    10.0 becky       4.0    4.0
skyler    10.0 becky       4.0    8.0
skyler    10.0 john        2.0    2.0

6 rows selected.

Let me know if this is what you wanted.

Mufasa
(aka Dave of Sandy, Utah, USA @ 06:16 (02Jan04) GMT, 23:16(01Jan04) Mountain Time)

 

[ddrillich]

From OCA/OCP Introduction to Oracle 9i SQL:

"The CUBE and ROLLUP modifiers to the GROUP BY clause allow you to create aggregations of aggregates, or superaggregates. These superaggregates or summary rows are included with the result set in a way similar to using the COMPUTE statement on control breaks in SQL*Plus; that is, they are included in the data and contain NULL values in the aggregated columns. ROLLUP creates hierarchical aggregates. CUBE creates aggregates for all combinations of columns specified."

In our case, please try:

select project,employee, sum(emp_hours)
from VBRookie
group by cube (project,employee)

which returns:

PROJECT EMPLOYEE SUM(EMP_HOURS)

(nil)    (nil)   14
(nil)    john   2
(nil)    becky  8
(nil)    brian  1.5
(nil)    alissa  2.5
quasar (nil)   4
quasar brian 1.5
quasar alissa 2.5
skyler  (nil)   10
skyler  john   2
skyler  becky  8

However, it doesn’t return the running subtotals – it returns the subtotals of any combinations of columns.

Hope it helps,
Dan

 

[ddrillich]

Just another remark about this issue: In think the combination of the function rank and the group by cube/rollup can produce any running subtotals.

 

[sem]

create table emp_hours(project varchar2(20) , employee varchar2(20), hours number);

insert into ....


select project ,
sum(hours) over (partition by project) proj_hours ,
employee ,
sum(hours) over (partition by employee) emp_hours
from emp_hours;

Regards, Dima

http://seminihin.narod.ru

 

[VBRookie]

Thanks a bunch to you all ... I think that I have it. I wanted to try and do it in a single query if at all possible. I had to play around with the rollup clause a little bit ...

select time_entry_seq_nbr wesn,
	   time_entry.project_id, 
	   client.name,
	   project.description, 
	   first_name, 
	   last_name,
	   TO_CHAR (time_entry.work_date, 'MM/DD/YYYY') WORK_DATE,
	   time_entry.task,
	   sum (hours) as total_hours, 
	   sum (dollars) as total_dollars,
	   nvl (time_entry.comments , '---') COMMENTS
from time_entry, project, client, resources
where time_entry.work_date between to_date ('12/25/2003', 'MM/DD/YYYY') and to_date ('12/31/2003', 'MM/DD/YYYY')
and time_entry.project_id = 527
and time_entry.project_Id = project.project_id
and time_entry.resource_id = resources.resource_id
and project.client_id = client.client_id 
group by rollup (last_name,
	  first_name,
	  work_date,
	  time_entry.task, 
	  comments, 
	  description,
	  client.name, 
  	  time_entry.project_id, 
	  time_entry_seq_nbr)
order by last_name, first_name, work_date

Thanks again,
VB Rookie

 

[sem]

VB Rookie, what's wrong with my suggestion? Have you tried it?

Regards, Dima

http://seminihin.narod.ru

 

[VBRookie]

Sem, nothing at all is wrong with yours. But when I saw ddrillich's post using Cube I researched that more and found Rollup which seemed to accomplish what I was looking for so I ran with it.

Thanks so much for your suggestion, it's very much appreciated. Hopefully I'll be able to post something that will be of assistance to you in the future. Although it probably won't be in the Oracle forum ... I'm more into .NET, C# and Web Design.

Thanks again,
- VB Rookie