where clause in select statement

homer.favenir · #1 (**permalink**) 06-11-08, 07:06

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

r937 · #2 (**permalink**) 06-11-08, 08:30

SUM(CASE WHEN time_end <= '16:00' THEN break_hours ELSE 0 END) as 'field1'

homer.favenir · #3 (**permalink**) 06-11-08, 21:39

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

homer.favenir · #4 (**permalink**) 06-12-08, 01:55

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

mike_bike_kite · #5 (**permalink**) 06-12-08, 04:21

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.

r937 · #6 (**permalink**) 06-12-08, 07:12

change SUM(...) to COALESCE(SUM(...),0)

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

Internet Services

Web Development

Digital Marketing

Consumer Tech