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 3 4388
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
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
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 This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics |
by: Scott |
last post by:
How can you show SUBTOTALS in the same column as the data, NOT in a
separate column?
I need to SUBTOTAL in the same column as one of the the data columns,
like the subtotals 700 and 800 under Field 3 below (I really have 21
columns). Also I must show one blank row after each resulting group:
Field1 Field2 Field3 Field4
entry1 data2.1 100 data4.1
entry2 data2.2 400 data4.2
|
by: Andy V |
last post by:
Hi. I've got a multi page report of my monthly purchases. I'd like a running
total (month to date) at the end of each page and to have that total at the
top of the following page. So far all I can figure out is a grand total at
the end of the entire report!
Any suggestions? Thanks.
|
by: John Baker |
last post by:
Hi:
I have a situation where I have a series of time records (one or more per day) for a
number of projects. Each record has an identification for the activity conducted during
the period recorded. For any project, the activity may be one of many things, and the
activities may vary by project number.
I need to run a report and end up with a list of all the times reported, and totals by the
project number and the activity, as well as...
|
by: Mark |
last post by:
Is there a way to put a subtotal in a datagrid? For example, let's say I
have a datagrid of sales for a given year. No paging. I've used the
ItemDataBound event of the datagrid to add totals at the bottom. I'd like
to add quarterly subtotals in the MIDDLE of the grid on their own rows. The
data is sorted by default by date, so after the last March record, I want to
insert a subtotal.
Which event (ItemDataBound or ItemCreated) is the...
|
by: Richard Grene |
last post by:
I am creating an Excel spreadsheet using a query function in my vb code. This works fine. I can format the columns but dont know how to create subtotals and a grand total.
Thanks,
Richard
| |
by: Jerry LeVan |
last post by:
Is is possible, via some clever sql coding ( perhaps with PL/pgsql)
to get subtotals to appear in a selection, ie
If I have a query: select * from checks order by category
I would like the have the subtotals appear (possibly in
an unused column for each "category" when the category
"breaks".
Basically I would like to meld the query:
select category, sum(amount) from checks group by category order by
|
by: Paul Ilacqua |
last post by:
I'm using VB .NET and I'm trying to "roll my own" report subtotals. I'm
building a string from a datareader top pass to a reporting class and I want
to break and subtotal QTY at each change in shipper and I have struggled
with the code to do so for days and just can't seem to get it. I can get it
to insert a draw a line at a shipper change, but I can't get the subtotals.
The actual Code is down below data sample. I appreciate any advice I...
|
by: prn |
last post by:
Hi folks,
I'm looking for a little advice here. I need to create a report that is totals only, with no detail records. I have a database with a lot of individuals (people) and the report has to have some overall totals, totals per state, totals by gender, race/ethnicity, and several other criteria. This report does not need to (and should not) report on each individual, just the totals.
I can create expressions for each of the items I...
|
by: =?Utf-8?B?SHV0dHk=?= |
last post by:
I have searched the net but have been unable to find a solution to
subtotaling in a gridview. I have a gridview that is bounded to sqldatasource
where I would like to subtotal rows throughout the gridview based on column
value "Type". I am using VB.NET. Thanks.
--
Hutty
|
by: marktang |
last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look !
Part I. Meaning of...
|
by: Hystou |
last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it.
First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
| |
by: Oralloy |
last post by:
Hello folks,
I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>".
The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed.
This is as boiled down as I can make it.
Here is my compilation command:
g++-12 -std=c++20 -Wnarrowing bit_field.cpp
Here is the code in...
|
by: jinu1996 |
last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth.
The Art of Business Website Design
Your website is...
|
by: tracyyun |
last post by:
Dear forum friends,
With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
|
by: TSSRALBI |
last post by:
Hello
I'm a network technician in training and I need your help.
I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs.
The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols.
I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
|
by: adsilva |
last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
|
by: 6302768590 |
last post by:
Hai team
i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
| |
by: bsmnconsultancy |
last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...
| |