OpenOffice.org Forum at OOoForum.orgThe OpenOffice.org Forum
 
 [Home]   [FAQ]   [Search]   [Memberlist]   [Usergroups]   [Register] 
 [Profile]   [Log in to check your private messages]   [Log in] 

sum of a field (SOLVED)

 
Post new topic   Reply to topic    OOoForum.org Forum Index -> OpenOffice.org Base
View previous topic :: View next topic  
Author Message
midlandwest
Newbie
Newbie


Joined: 13 Dec 2007
Posts: 2
Location: USA
PostPosted: Thu Dec 13, 2007 9:54 am    Post subject: sum of a field (SOLVED) Reply with quote
Hello,

I am new to Base. I have used Access in the past.

I am trying to get the sum of a group of rows in a column. The query is copied below:

SELECT "Job_ Number", "EmployeeID", "TaskID", "TimeEntry"."Stop_Time" - "TimeEntry"."Start_Time" AS "Hours" FROM "TimeEntry" ORDER BY "Job_ Number" ASC

I need to get the sum of the calculated areas grouped by Job_Number.

I realize this is probably simple, but I am struggling mightily. I have looked through the "Getting Started" manual, and through several posts, but have not found how to do this.

I will appreciate any help.

Last edited by midlandwest on Fri Dec 14, 2007 7:10 am; edited 1 time in total
Back to top
View user's profile Send private message
Sliderule
Super User
Super User


Joined: 29 May 2004
Posts: 2477
Location: 3rd Rock From The Sun
PostPosted: Thu Dec 13, 2007 2:45 pm    Post subject: Reply with quote
midlandwest:

You have asked a few questions here, I think, and, I will attempt to answer them as best as possible.

First, I will assume, you are using the embedded OpenOffice database HSQL, rather then Microsoft Access ( that you mentioned you have used in the past ). Additionally, since I do not have your 'exact' database definition, a few more assumptions. I am presuming that "TimeEntry"."Start_Time" and "TimeEntry"."Stop_Time" are defined in your database table with a column type of TIME.

First 'issue' with HSL as of this moment ( until they release the newest version, and, OpenOffice updates to it ) . . . DATE and TIME arithmetic is NOT 'directly' available. But, there is a work-around. That workaround is the DATEDIFF function. According to HSQL documentation that can be found here http://www.hsqldb.org/doc/guide/ch09.html :

HSQL Documentation http://www.hsqldb.org/doc/guide/ch09.html wrote:
DATEDIFF(string, datetime1, datetime2)

returns the count of units of time elapsed from datetime1 to datetime2. The string indicates the unit of time and can have the following values 'ms'='millisecond', 'ss'='second','mi'='minute','hh'='hour', 'dd'='day', 'mm'='month', 'yy' = 'year'. Both the long and short form of the strings can be used.

So, in order to 'calculate' the hours . . . something like
Code:
DATEDIFF( 'MINUTE', "Start_Time", "Stop_Time" ) / 60.00000

will return the difference between the two TIMES as hours - with minutes as a decimal ( it will calculate minutes, then, get hours by division . . . and . . . to compensate for 'rounding' . . . divide it by 60.00000 ).

Now, you want to use GROUP BY in your SQL ( or Query ). You said:
midlandwest wrote:
I need to get the sum of the calculated areas grouped by Job_Number.


For the following example . . . I created an example database . . . it looks like this:

I am not sure what "sum of the calculated areas" means . . . BUT . . . perhaps this will help. If you are using the GUI ( Graphics User Interface ) to create your query . . . and you ONLY wanted to SUM the hours by Job Number (GROUP):


The SQL would be:
Code:
SELECT
   "Job_Number",
   SUM( DATEDIFF( 'MINUTE', "Start_Time", "Stop_Time" ) / 60.00 ) AS "Total Time By Job_Number"
FROM "TimeEntry" AS "TimeEntry"
GROUP BY "Job_Number"

If you wanted to "SUM" the hours by both Job Number and Employee ID ( GROUP):


The SQL would be:
Code:
SELECT
   "Job_Number",
   "Employee_ID",
   "TaskID",
    SUM( DATEDIFF( 'minute', "Start_Time", "Stop_Time" ) / 60.00000 ) AS "Total Time BY Job_Nubmer and Emp_ID"
FROM "TimeEntry" AS "TimeEntry"
GROUP BY "Job_Number", "Employee_ID", "TaskID"
ORDER By "Job_Number"

I hope this helps, please be sure ot let me / us know.

Sliderule

Thanks to add [Solved] in your first post title (edit button) if your issue has been fixed / resolved.
Back to top
View user's profile Send private message
midlandwest
Newbie
Newbie


Joined: 13 Dec 2007
Posts: 2
Location: USA
PostPosted: Fri Dec 14, 2007 7:07 am    Post subject: Reply with quote
Thank you very much. Your help is greatly appreciated.

This will help. I think the rest of what I'm looking for is actually in report builder (which I just downloaded yesterday).

What I'm looking for is a report in the format:

Job No: 071212

Emp Task Hours Description
0123 10 2 xxxxxxxxxxx
0123 10 2 xxxxxxxxxxx
0123 12 3.5 xxxxxxxxxxxxxxx
0226 91 2.6 xxxxxxxxxxxxxxxx
0226 92 3.5 xxxxxxxxxxxxxxxx
Total Hours: 13.6

Job No: 071256

Emp Task Hours Description
0123 10 2 xxxxxxxxxxx
0123 10 2 xxxxxxxxxxx
0123 12 3.5 xxxxxxxxxxxxxxx
0226 91 2.6 xxxxxxxxxxxxxxxx
0226 92 3.5 xxxxxxxxxxxxxxxx
Total Hours: 13.6
Back to top
View user's profile Send private message
Display posts from previous:   
Post new topic   Reply to topic    OOoForum.org Forum Index -> OpenOffice.org Base All times are GMT - 8 Hours
Page 1 of 1

 
Jump to:  
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum


Powered by phpBB © 2001, 2005 phpBB Group