Well, first you have posted your question in the wrong forum. You should have posted your question in the forum.
The second problem I see is that you are being too generic when you have your employees enter their night shift hours worked. If you are able, I recommend you modify your table to record hours seperately from minutes and make the columns of type NUMBER instead of type VARCHAR2(). Then you can use simply arithmatic to total the hours and minutes worked.
If you are locked into your table and can't change it, then you can convert the characters to numbers and then perform your summary arithmatic on the values. For example:
1 with tab1 as (
2 select 10 as empno, '01:00' as nightshifthrs from dual union all
3 select 10 as empno, '05:00' as nightshifthrs from dual union all
4 select 10 as empno, '08:00' as nightshifthrs from dual union all
5 select 10 as empno, '10:00' as nightshifthrs from dual union all
6 select 10 as empno, '07:00' as nightshifthrs from dual union all
7 select 10 as empno, '09:00' as nightshifthrs from dual)
8 select sum(to_number(replace(nightshifthrs,':','.'))) AS hours_worked
9* from tab1
SQL> /
HOURS_WORKED
------------
40
SQL>
Of course, if your users can and do enter minutes, then that complicates the example I provided. You will have to convert the minutes to decimal, sum the amount, then convert the decimal back to time and add this to your hours. For example:
1 with tab1 as (
2 select 10 as empno, '01:15' as nightshifthrs from dual union all
3 select 10 as empno, '05:00' as nightshifthrs from dual union all
4 select 10 as empno, '08:30' as nightshifthrs from dual union all
5 select 10 as empno, '10:00' as nightshifthrs from dual union all
6 select 10 as empno, '07:45' as nightshifthrs from dual union all
7 select 10 as empno, '09:00' as nightshifthrs from dual)
8 select sum(to_number(substr(nightshifthrs,1,2))) + SUM(to_number(SUBSTR(nightshifthrs,4,5)))/60
9* from tab1
SQL> /
HOURS_WORKED
------------
41.5
SQL>
Hope this helps.
Craig...