467,169 Members | 974 Online
Bytes | Developer Community
Ask Question

Home New Posts Topics Members FAQ

Post your question to a community of 467,169 developers. It's quick & easy.

Subtotals and Totals

I have a database that tracks the vacation time of each employee of the
company that I work for. Everything works great and totals all of the
vacation time that has been used. Could someone give me a little
guidance on how I could get a subtotal for each employee and then total
all of the subtotals? I would like to list employee 1's time and total
it, then employee 2's time and total it and so on. Then total all of
the subtotals. Not sure where to start to create the loop for each
different employee.

Thanks in advance for your help,

Greg

Mar 25 '06 #1
  • viewed: 4009
Share:
3 Replies
On Fri, 24 Mar 2006 18:23:03 -0800, Greg wrote:
I have a database that tracks the vacation time of each employee of the
company that I work for. Everything works great and totals all of the
vacation time that has been used. Could someone give me a little
guidance on how I could get a subtotal for each employee and then total
all of the subtotals? I would like to list employee 1's time and total
it, then employee 2's time and total it and so on. Then total all of
the subtotals. Not sure where to start to create the loop for each
different employee.

Thanks in advance for your help,

Greg

Here is a select from the famous EMP & DEPT tables which does something
similar to what you want:

WITH sums AS
(SELECT deptno,
SUM(sal) AS salary
FROM emp
GROUP BY rollup(deptno))
SELECT d.deptno,
d.loc,
s.salary
FROM sums s,
dept d
WHERE s.deptno = d.deptno(+)

Results look like this:

DEPTNO LOC SALARY
---------- ------------- ----------
10 NEW YORK 8750
20 DALLAS 10875
30 CHICAGO 9400
29025

SQL>
The critical part, which provides subtotals and the final total is
"GROUP BY ROLLUP". This, however, depends on the type of the database
you are using. The statement above is from Oracle RDBMS, with version
being at least 9i. In lower versions subquery factoring (WITH ...) doesn't
work. I'm not sure about the GROUP BY ROLLUP part either. So, if your
database is not Oracle, get yourself Oracle 10XE. It is free, you can
stuff 4GB in it and you can do things like the query above with it.
It supports subquery factoring, group by rollup and much, much more.

--
http://www.mgogala.com

Mar 25 '06 #2
Sorry, I should have given more specifics. I'm using PHP to access a
MySQL database on a Windows 2000 server. What I have developed gives a
long list of employee names, dates and hours with a total at the
bottom. What I would like to do is have a break between each employee
and total their hours, then have the grand total at the bottom.

Thanks,

Greg

Mar 26 '06 #3
On Sat, 25 Mar 2006 18:47:38 -0800, Greg wrote:
Sorry, I should have given more specifics. I'm using PHP to access a
MySQL database on a Windows 2000 server. What I have developed gives a
long list of employee names, dates and hours with a total at the
bottom. What I would like to do is have a break between each employee
and total their hours, then have the grand total at the bottom.


Please disregard my reply. Wrong kind of database.

--
http://www.mgogala.com

Mar 27 '06 #4

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

2 posts views Thread by Scott | last post: by
1 post views Thread by Andy V | last post: by
reply views Thread by Mark | last post: by
2 posts views Thread by Richard Grene | last post: by
4 posts views Thread by Jerry LeVan | last post: by
reply views Thread by Paul Ilacqua | last post: by
2 posts views Thread by =?Utf-8?B?SHV0dHk=?= | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.