Home   |   Register   |   Rules   |   Calendar   |   Get Daily   |     |  
 


If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

 
Go Back  dBforums > Database Server Software > MySQL > sum the assigned variables

Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1 (permalink)  
Old 05-23-08, 01:12
homer.favenir homer.favenir is offline
Registered User
  
Join Date: Oct 2007
Location: Manila, Philippines
Posts: 132
sum the assigned variables
hi to all,
how can i sum the assigned variable 'Over Time'?
e.g.
Code:
if(key_entry_log.time_start and key_entry_log.time_end <= '16:00', 'no ot',
  if(key_entry_log.time_start <= '16:00' and key_entry_log.time_end >= '16:00',
    subtime(key_entry_log.time_end,'16:00'),
      if(key_entry_log.time_start and key_entry_log.time_end > '16:00', subtime(key_entry_log.time_end,
        key_entry_log.time_start), "no ot"))) as 'Over Time'
this script works very fine, it gets the over time per record,
but i need them to be sum as a whole.

i already grouped them

thanks!
__________________
Take Nothing But Pictures;
Leave Nothing But Footprints;
Kill Nothing But Time;
Reply With Quote
  #2 (permalink)  
Old 05-23-08, 04:12
gvee gvee is offline
www.gvee.co.uk
  
Join Date: Jan 2007
Location: UK
Posts: 10,156
How do you sum "no ot"?
__________________
George
Twitter | Blog
Reply With Quote
  #3 (permalink)  
Old 05-23-08, 04:59
homer.favenir homer.favenir is offline
Registered User
  
Join Date: Oct 2007
Location: Manila, Philippines
Posts: 132
i dont sum 'no ot', but when i replace it with ' ' or '0', no value is returned.
my ot's value is placed in 'Overt Time'.
i cant sum my Over Time.
please advice
thanks
__________________
Take Nothing But Pictures;
Leave Nothing But Footprints;
Kill Nothing But Time;
Reply With Quote
  #4 (permalink)  
Old 05-23-08, 05:30
gvee gvee is offline
www.gvee.co.uk
  
Join Date: Jan 2007
Location: UK
Posts: 10,156
What I'm getting at is that you're returning non numeric values, which cannot be summed!

It may be clearer if you post results from your query above along with desired results from summing.
__________________
George
Twitter | Blog
Reply With Quote
  #5 (permalink)  
Old 05-23-08, 06:28
homer.favenir homer.favenir is offline
Registered User
  
Join Date: Oct 2007
Location: Manila, Philippines
Posts: 132
the results from my query:
Code:
'2008-05-22', 'Gratuito', 'Andrea', 'b', 'BAPCO/BSA', 'YELLOW', 'SC29024', 'BSA1010 PASADENA/ARCADIA-YBC 02/08', 07:00:00, 07:16:00, 'no ot'
'2008-05-22', 'Gratuito', 'Andrea', 'b', 'BAPCO/BSA', 'YELLOW', 'SC29024', 'BSA1010 PASADENA/ARCADIA-YBC 02/08', 07:16:00, 07:29:00, 'no ot'
'2008-05-22', 'Gratuito', 'Andrea', 'b', 'BAPCO/BSA', 'YELLOW', 'SC29024', 'BSA1010 PASADENA/ARCADIA-YBC 02/08', 07:29:00, 07:33:00, 'no ot'
'2008-05-22', 'Gratuito', 'Andrea', 'b', 'BAPCO/BSA', 'YELLOW', 'SC29024', 'BSA1010 PASADENA/ARCADIA-YBC 02/08', 07:33:00, 07:45:00, 'no ot'
'2008-05-22', 'Gratuito', 'Andrea', 'b', 'BAPCO/BSA', 'YELLOW', 'SC29024', 'BSA1010 PASADENA/ARCADIA-YBC 02/08', 07:45:00, 07:58:00, 'no ot'
'2008-05-22', 'Gratuito', 'Andrea', 'b', 'BAPCO/BSA', 'YELLOW', 'SC29024', 'BSA1010 PASADENA/ARCADIA-YBC 02/08', 07:58:00, 08:09:00, 'no ot'
'2008-05-22', 'Gratuito', 'Andrea', 'b', 'BAPCO/BSA', 'YELLOW', 'SC29025', 'BSA1011 MADISON-YBC 04/08', 08:09:00, 08:24:00, 'no ot'
'2008-05-22', 'Gratuito', 'Andrea', 'b', 'BAPCO/BSA', 'YELLOW', 'SC29025', 'BSA1011 MADISON-YBC 04/08', 08:24:00, 08:35:00, 'no ot'
'2008-05-22', 'Gratuito', 'Andrea', 'b', 'BAPCO/BSA', 'YELLOW', 'SC29025', 'BSA1011 MADISON-YBC 04/08', 08:35:00, 08:49:00, 'no ot'
'2008-05-22', 'Gratuito', 'Andrea', 'b', 'BAPCO/BSA', 'YELLOW', 'SC29025', 'BSA1011 MADISON-YBC 04/08', 08:49:00, 09:21:00, 'no ot'
'2008-05-22', 'Gratuito', 'Andrea', 'b', 'BAPCO/BSA', 'YELLOW', 'SC29026', 'BSA1012 SAN FRANCISCO-VYP 04/08', 09:21:00, 09:44:00, 'no ot'
'2008-05-22', 'Gratuito', 'Andrea', 'b', 'BAPCO/BSA', 'YELLOW', 'SC29026', 'BSA1012 SAN FRANCISCO-VYP 04/08', 09:44:00, 10:09:00, 'no ot'
'2008-05-22', 'Gratuito', 'Andrea', 'b', 'BAPCO/BSA', 'YELLOW', 'SC29027', 'BSA1013 AUBURN-VYP 02/08', 10:09:00, 10:30:00, 'no ot'
'2008-05-22', 'Gratuito', 'Andrea', 'b', 'BAPCO/BSA', 'YELLOW', 'SC29027', 'BSA1013 AUBURN-VYP 02/08', 10:30:00, 10:47:00, 'no ot'
'2008-05-22', 'Gratuito', 'Andrea', 'b', 'BAPCO/BSA', 'YELLOW', 'SC29027', 'BSA1013 AUBURN-VYP 02/08', 10:47:00, 11:09:00, 'no ot'
'2008-05-22', 'Gratuito', 'Andrea', 'b', 'BAPCO/BSA', 'YELLOW', 'SC29027', 'BSA1013 AUBURN-VYP 02/08', 11:09:00, 11:23:00, 'no ot'
'2008-05-22', 'Gratuito', 'Andrea', 'b', 'UFPB', 'WHITE', 'SC29086A', 'UFPB-WP/YOUNGSTOWN-WARREN REGIONAL-W-BUSINESS ATT 04/08', 11:23:00, 11:45:00, 'no ot'
'2008-05-22', 'Gratuito', 'Andrea', 'b', 'UFPB', 'WHITE', 'SC29086A', 'UFPB-WP/YOUNGSTOWN-WARREN REGIONAL-W-BUSINESS ATT 04/08', 11:45:00, 13:03:00, 'no ot'
'2008-05-22', 'Gratuito', 'Andrea', 'b', 'UFPB', 'WHITE', 'SC29086A', 'UFPB-WP/YOUNGSTOWN-WARREN REGIONAL-W-BUSINESS ATT 04/08', 13:03:00, 13:20:00, 'no ot'
'2008-05-22', 'Gratuito', 'Andrea', 'b', 'UFPB', 'WHITE', 'SC29086A', 'UFPB-WP/YOUNGSTOWN-WARREN REGIONAL-W-BUSINESS ATT 04/08', 13:20:00, 13:31:00, 'no ot'
'2008-05-22', 'Gratuito', 'Andrea', 'b', 'UFPB', 'WHITE', 'SC29086A', 'UFPB-WP/YOUNGSTOWN-WARREN REGIONAL-W-BUSINESS ATT 04/08', 13:31:00, 13:46:00, 'no ot'
'2008-05-22', 'Gratuito', 'Andrea', 'b', 'UFPB', 'WHITE', 'SC29086A', 'UFPB-WP/YOUNGSTOWN-WARREN REGIONAL-W-BUSINESS ATT 04/08', 13:46:00, 13:58:00, 'no ot'
'2008-05-22', 'Gratuito', 'Andrea', 'b', 'UFPB', 'WHITE', 'SC29086A', 'UFPB-WP/YOUNGSTOWN-WARREN REGIONAL-W-BUSINESS ATT 04/08', 13:58:00, 14:13:00, 'no ot'
'2008-05-22', 'Gratuito', 'Andrea', 'b', 'UFPB', 'WHITE', 'SC29086A', 'UFPB-WP/YOUNGSTOWN-WARREN REGIONAL-W-BUSINESS ATT 04/08', 14:13:00, 14:26:00, 'no ot'
'2008-05-22', 'Gratuito', 'Andrea', 'b', 'UFPB', 'WHITE', 'SC29086A', 'UFPB-WP/YOUNGSTOWN-WARREN REGIONAL-W-BUSINESS ATT 04/08', 14:26:00, 14:46:00, 'no ot'
'2008-05-22', 'Gratuito', 'Andrea', 'b', 'UFPB', 'WHITE', 'SC29086A', 'UFPB-WP/YOUNGSTOWN-WARREN REGIONAL-W-BUSINESS ATT 04/08', 14:46:00, 15:03:00, 'no ot'
'2008-05-22', 'Gratuito', 'Andrea', 'b', 'UFPB', 'WHITE', 'SC29086A', 'UFPB-WP/YOUNGSTOWN-WARREN REGIONAL-W-BUSINESS ATT 04/08', 15:03:00, 15:43:00, 'no ot'
'2008-05-22', 'Gratuito', 'Andrea', 'b', 'UFPB', 'WHITE', 'SC29086A', 'UFPB-WP/YOUNGSTOWN-WARREN REGIONAL-W-BUSINESS ATT 04/08', 15:43:00, 15:58:00, 'no ot'
'2008-05-22', 'Gratuito', 'Andrea', 'b', 'UFPB', 'WHITE', 'SC29086A', 'UFPB-WP/YOUNGSTOWN-WARREN REGIONAL-W-BUSINESS ATT 04/08', 15:58:00, 16:24:00, '00:24:00'
'2008-05-22', 'Gratuito', 'Andrea', 'b', 'UFPB', 'WHITE', 'SC29086A', 'UFPB-WP/YOUNGSTOWN-WARREN REGIONAL-W-BUSINESS ATT 04/08', 16:24:00, 16:38:00, '00:14:00'
'2008-05-22', 'Gratuito', 'Andrea', 'b', 'UFPB', 'WHITE', 'SC29086A', 'UFPB-WP/YOUNGSTOWN-WARREN REGIONAL-W-BUSINESS ATT 04/08', 17:37:00, 17:55:00, '00:18:00'
'2008-05-22', 'Gratuito', 'Andrea', 'b', 'UFPB', 'WHITE', 'SC29086B', 'UFPB-WP/YOUNGSTOWN-WARREN REGIONAL-W-RESIDENTIAL ATT 04/08', 16:38:00, 17:12:00, '00:34:00'
'2008-05-22', 'Gratuito', 'Andrea', 'b', 'UFPB', 'WHITE', 'SC29086B', 'UFPB-WP/YOUNGSTOWN-WARREN REGIONAL-W-RESIDENTIAL ATT 04/08', 17:12:00, 17:37:00, '00:25:00'
'2008-05-22', 'Gratuito', 'Andrea', 'b', 'UFPB', 'WHITE', 'SC29086B', 'UFPB-WP/YOUNGSTOWN-WARREN REGIONAL-W-RESIDENTIAL ATT 04/08', 17:55:00, 18:39:00, '00:44:00'
'2008-05-22', 'Gratuito', 'Andrea', 'b', 'UFPB', 'WHITE', 'SC29086B', 'UFPB-WP/YOUNGSTOWN-WARREN REGIONAL-W-RESIDENTIAL ATT 04/08', 18:39:00, 19:02:00, '00:23:00'
'2008-05-22', 'Gratuito', 'Andrea', 'b', 'UFPB', 'WHITE', 'SC29086B', 'UFPB-WP/YOUNGSTOWN-WARREN REGIONAL-W-RESIDENTIAL ATT 04/08', 19:02:00, 19:22:00, '00:20:00'
'2008-05-22', 'Gratuito', 'Andrea', 'b', 'UFPB', 'WHITE', 'SC29086B', 'UFPB-WP/YOUNGSTOWN-WARREN REGIONAL-W-RESIDENTIAL ATT 04/08', 19:22:00, 20:00:00, '00:38:00'
the computation is ok,
but i need to sum the overtime column
my script is:
Code:
SELECT
key_entry_log.`date`,
key_entry_log.last_name,
`user`.tl,
`user`.`group`,
batch_log.`client`,
batch_log.`service`,
key_entry_log.job_no,
key_entry_log.job_name,
key_entry_log.time_start,
key_entry_log.time_end,

if(key_entry_log.time_start and key_entry_log.time_end <= '16:00', 'no ot',
  if(key_entry_log.time_start <= '16:00' and key_entry_log.time_end >= '16:00',
    subtime(key_entry_log.time_end,'16:00'),
      if(key_entry_log.time_start and key_entry_log.time_end > '16:00', subtime(key_entry_log.time_end,
        key_entry_log.time_start),''))) as 'Over Time'



FROM
key_entry_log
inner join `user` on key_entry_log.last_name = `user`.last_name
inner join batch_log on key_entry_log.job_no = batch_log.job_number

where batch_log.total_batches is not null and key_entry_log.last_name = 'gratuito'
and key_entry_log.`date` = '2008-05-22'


order by
key_entry_log.`date` desc
any advice?

thanks
__________________
Take Nothing But Pictures;
Leave Nothing But Footprints;
Kill Nothing But Time;
Reply With Quote
  #6 (permalink)  
Old 05-23-08, 07:52
r937 r937 is offline
SQL Consultant
  
Join Date: Apr 2002
Location: Toronto, Canada
Posts: 19,525
Quote:
Originally Posted by homer.favenir
any advice?
use NULL, not 'no ot'

aggregate functions like SUM() ignore NULLs
__________________
r937.com | rudy.ca
please visit Simply SQL and buy my book
Reply With Quote
  #7 (permalink)  
Old 05-23-08, 08:15
homer.favenir homer.favenir is offline
Registered User
  
Join Date: Oct 2007
Location: Manila, Philippines
Posts: 132
the sum has no value, it didnt sum the 'Over Time'

Code:
'2008-05-22', 'Gratuito', 'Andrea', 'b', 'BAPCO/BSA', 'YELLOW', 'SC29024', 'BSA1010 PASADENA/ARCADIA-YBC 02/08', 07:00:00, 07:16:00, 
'2008-05-22', 'Gratuito', 'Andrea', 'b', 'BAPCO/BSA', 'YELLOW', 'SC29025', 'BSA1011 MADISON-YBC 04/08', 08:09:00, 08:24:00, 
'2008-05-22', 'Gratuito', 'Andrea', 'b', 'BAPCO/BSA', 'YELLOW', 'SC29026', 'BSA1012 SAN FRANCISCO-VYP 04/08', 09:21:00, 09:44:00, 
'2008-05-22', 'Gratuito', 'Andrea', 'b', 'BAPCO/BSA', 'YELLOW', 'SC29027', 'BSA1013 AUBURN-VYP 02/08', 10:09:00, 10:30:00, 
'2008-05-22', 'Gratuito', 'Andrea', 'b', 'UFPB', 'WHITE', 'SC29086A', 'UFPB-WP/YOUNGSTOWN-WARREN REGIONAL-W-BUSINESS ATT 04/08', 11:23:00, 11:45:00, 0
'2008-05-22', 'Gratuito', 'Andrea', 'b', 'UFPB', 'WHITE', 'SC29086B', 'UFPB-WP/YOUNGSTOWN-WARREN REGIONAL-W-RESIDENTIAL ATT 04/08', 16:38:00, 17:12:00, 0
the first 4 rows has null value and the last 2 rows has '0' value
it didnt sum the computed overtime

Code:
sum(if(key_entry_log.time_start and key_entry_log.time_end <= '16:00', NULL,
  if(key_entry_log.time_start <= '16:00' and key_entry_log.time_end >= '16:00',
    subtime(key_entry_log.time_end,'16:00'),
      if(key_entry_log.time_start and key_entry_log.time_end > '16:00', subtime(key_entry_log.time_end,
        key_entry_log.time_start),NULL)))) as 'Over Time'
__________________
Take Nothing But Pictures;
Leave Nothing But Footprints;
Kill Nothing But Time;
Last edited by homer.favenir; 05-23-08 at 08:18.
Reply With Quote
  #8 (permalink)  
Old 05-23-08, 08:19
r937 r937 is offline
SQL Consultant
  
Join Date: Apr 2002
Location: Toronto, Canada
Posts: 19,525
maybe instead of trying to sum times, you should be calculating the overtime as integer minutes and summing those, converting the sum back to a time
__________________
r937.com | rudy.ca
please visit Simply SQL and buy my book
Reply With Quote
  #9 (permalink)  
Old 05-23-08, 21:51
homer.favenir homer.favenir is offline
Registered User
  
Join Date: Oct 2007
Location: Manila, Philippines
Posts: 132
i did it,
i just convert the computed subtime to seconds,
sum it and convert it again from seconds to time.
just what youve said.
thanks

Code:
sec_to_time(sum(if(key_entry_log.time_start and key_entry_log.time_end <= '16:00', '',
  if(key_entry_log.time_start <= '16:00' and key_entry_log.time_end >= '16:00',
     TIME_TO_SEC(subtime(key_entry_log.time_end,'16:00')),
      if(key_entry_log.time_start and key_entry_log.time_end > '16:00', TIME_TO_SEC(subtime(key_entry_log.time_end,
        key_entry_log.time_start)),''))))) as 'Over Time'
__________________
Take Nothing But Pictures;
Leave Nothing But Footprints;
Kill Nothing But Time;
Reply With Quote
  #10 (permalink)  
Old 05-23-08, 22:18
r937 r937 is offline
SQL Consultant
  
Join Date: Apr 2002
Location: Toronto, Canada
Posts: 19,525
well, it isn't just what i said, because i said to use minutes and expecially NULLs (not empty strings)

but i'm glad you got it sorted
__________________
r937.com | rudy.ca
please visit Simply SQL and buy my book
Reply With Quote
Reply

« Previous Thread | Next Thread »
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes
Linear Mode Linear Mode

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts
BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


 
 
 
Home  |   Register  |   Get New  |   FAQ's  |   Sitemap

Powered by vBulletin
Copyright ©2000 - 2012, Jelsoft Enterprises Ltd.