Newbie SQL question

I am relatively new to SQL so this is probably a simplistic question.

I have a table with each record containing a number value representing seconds. I want to be able to sum all seconds and display it in the format HH:MM:SS for the summed number of seconds. The format needs to zero pad the hours, minutes and seconds if it is only 1 digit.

Example 05:01:20 not 5:1:20

I have devised an SQL script to do all this but it seems clumsy there has to be another ( simpler ) way to do this.

My solution is

To make it simple assume the table definition

Table A

        secs number;

select	decode (LEAST(SUM(secs)/3600,10),10,
		SUM(secs)/3600,=20


		'0' || SUM(secs)/3600 ) ) || ':'


	decode (LEAST(MOD(SUM(secs),3600)/60,10),
		10, MOD(SUM(secs),3600)/60,


		'0' || MOD(SUM(secs),3600/60) ) || ':'


	decode (LEAST(MOD(MOD(SUM(secs),3600),60),10),
		10, MOD(MOD(SUM(secs),3600),60),


		'0' || MOD(MOD(SUM(secs),3600),60) ) "Total Time"

I translated the SQL statement quickly from memory so I hope it is syntactically (sp?) correct. Basically, I compute the hours, minutes and seconds by the following:

hours =3D TotalSeconds / 3600
minutes =3D (TotalSeconds % 3600) / 60
seconds =3D TotalSeconds % 3600 % 60

This SQL statement is ugly and inefficient. What other alternatives are there to solve this problem in SQL only no PL/SQL.=20

Also, will Oracle compute the sum for each of the sum functions? What are the performace penalities for this method versuses others?

Any help would be appreciated. Received on Fri Nov 15 1996 - 00:00:00 CET