Results 1 to 10 of 10
  1. #1
    Join Date
    Oct 2007
    Location
    Manila, Philippines
    Posts
    132

    Unanswered: 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;

  2. #2
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    How do you sum "no ot"?
    George
    Home | Blog

  3. #3
    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;

  4. #4
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    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
    Home | Blog

  5. #5
    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;

  6. #6
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by homer.favenir
    any advice?
    use NULL, not 'no ot'

    aggregate functions like SUM() ignore NULLs
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  7. #7
    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'
    Last edited by homer.favenir; 05-23-08 at 09:18.
    Take Nothing But Pictures;
    Leave Nothing But Footprints;
    Kill Nothing But Time;

  8. #8
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  9. #9
    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;

  10. #10
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •