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

    Unanswered: where clause in select statement

    hi to all,
    can anyone please tell me how to separate 1 field and make it to 2 fields.
    i have break_hours field, and time_end field.
    i need to compute the sum of break_hours if time_end <= '16:00', for field1,
    and i need to compute the sum of break_hours if time_end > '16:00' for field2,
    so i have 2 fields from 1 field.
    Code:
    select
    `Date`,
    last_name,
    (sum(break_hours) where time_end <= '16:00') as 'field1',
    (sum(break_hours) where time_end > '16:00') as 'field2'
    
    FROM
    coding_log
    
    Group By
    `Date`,
    Last_Name
    is this correct? cause it doesnt work.
    please advice.

    thanks
    Take Nothing But Pictures;
    Leave Nothing But Footprints;
    Kill Nothing But Time;

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    SUM(CASE WHEN time_end <= '16:00' THEN break_hours ELSE 0 END) as 'field1'
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Oct 2007
    Location
    Manila, Philippines
    Posts
    132
    thanks! it works!

    now my code is this
    Code:
    SELECT
    coding_log.`Date`,
    `user`.Last_Name,
    `user`.tl,
    sum(coding_log.Total_Batches_Coded) AS 'Batches Coded/Day',
    
    format(sum(coding_log.Total_Batches_Coded) / (sum(time_to_sec(coding_log.time_end) - time_to_sec(coding_log.time_start))/3600),2) AS 'Total Batches/Hr',
    
    
    SUM(CASE WHEN time_end <= '16:00' THEN break_hours ELSE 0 END) as 'Total Regular Break',
    SUM(CASE WHEN time_end > '16:00' THEN break_hours ELSE 0 END) as 'Total OT Break',
    
    (format((sum(time_to_sec(coding_log.time_end) - time_to_sec(coding_log.time_start))/3600),2)) - (sum(format(if(coding_log.time_start and coding_log.time_end <= '16:00', '',
    if(coding_log.time_start <= '16:00' and coding_log.time_end >= '16:00',
    TIME_TO_SEC(subtime(coding_log.time_end,'16:00')),
    if(coding_log.time_start and coding_log.time_end > '16:00',
    TIME_TO_SEC(subtime(coding_log.time_end, coding_log.time_start)),'')))/3600,2))) as 'Total Regular Hours',
    
    sum(format(if(coding_log.time_start and coding_log.time_end <= '16:00', '',
    if(coding_log.time_start <= '16:00' and coding_log.time_end >= '16:00',
    TIME_TO_SEC(subtime(coding_log.time_end,'16:00')),
    if(coding_log.time_start and coding_log.time_end > '16:00',
    TIME_TO_SEC(subtime(coding_log.time_end, coding_log.time_start)),'')))/3600,2)) as 'Over Time'
    
    
    FROM
    coding_log
    INNER JOIN `user` ON (coding_log.last_name = `user`.last_name)
    where coding_log.last_name = 'checa' and coding_log.`date` = '2008-06-10'
    
    Group By
    coding_log.`Date`,
    `user`.Last_Name,
    `user`.tl
    
    Order By
    coding_log.`Date` DESC,
    `user`.tl,
    coding_log.Last_Name
    Take Nothing But Pictures;
    Leave Nothing But Footprints;
    Kill Nothing But Time;

  4. #4
    Join Date
    Oct 2007
    Location
    Manila, Philippines
    Posts
    132
    hi,

    i still have a question,
    this code works fine it returns 0.00 when there is no adjustment_time
    Code:
    format(time_to_sec(SUM(CASE WHEN coding_log.time_end <= '16:00' THEN coding_log.adjustment_time ELSE 0 END))/3600,2) as 'Total Regular Adjustment'
    however
    the code below returns 'null' when there is no adjustment_time, i need it to return to 0.00 cause i use it to compute for another formula. i used the code above and replace the <= with > but it always return 'null' when adjustment_date is empty, unlike the code above.
    Code:
    format(time_to_sec(SUM(CASE WHEN coding_log.time_end > '16:00' THEN coding_log.adjustment_time ELSE 0 END))/3600,2) as 'Total OT Adjustment',
    please advice.

    thanks
    Take Nothing But Pictures;
    Leave Nothing But Footprints;
    Kill Nothing But Time;

  5. #5
    Join Date
    Jun 2007
    Location
    London
    Posts
    2,527
    Homer

    Read up on NULLs and look out for the ifnull statement.

    thanks
    ____________
    Ask nothing but questions;
    Don't waste time & money on a book;
    And never kill time on google.

  6. #6
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    change SUM(...) to COALESCE(SUM(...),0)

    COALESCE is standard SQL, and therefore much better to use than IFNULL

    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
  •