Please correct my Query

Question

Select empno,sum(cast(substring(othours,0,3)as float)) as
Hour,sum(cast(substring(othours,4,5)as float)) as Minutes from tmstrans 
group by empno,othours

it is showing data like following

• 1 , 10
• 1 , 30
• 2 , 10
• 2 , 30

i need data like following

• 1 , 40
• 2 , 40

please help

Answer 1

Assuming you want to SUM both Hour and Minutes columns, you've to remove the othours column from the GROUP BY clause. Test it like this:

SELECT empno,
       SUM(CAST(SUBSTRING(othours,0,3) AS float)) AS Hour,
       SUM(CAST(SUBSTRING(othours,4,5) AS float)) AS Minutes
FROM tmstrans 
GROUP BY empno