Help With SQL

[logic4fun]

Hi all,
I am new to this world of SQL fun & DB2 World and am trying to optimize my things and learn things..i have the following table :

Ctype calltime holdtime
0 256 120
1 123 234
0 88 121
0 56 10
1 121 68

from the above table i want to derive the following

TOTAL_CALL_ANSWERED (this is when the Ctype is 0 i.e. count of 0 's in Ctype column)
TOTAL_CALL_CANCELLED (this is when the Ctype is 1)
TOTAL_CALLTIME
TOTAL_HOLDTIME
AVG_CALLTIME( total_calltime/total_call_answered)
AVG_HOLDTIME( total_holdtime/total_call_answered)


For the above what i did is i wrote a Embedded sql program in C where i get individual values Using query

select calltime,holdtime,CASE when Ctype = 0 then 'A'
when Ctype = 1 then 'C' END
from <table Name>;

and then i am taking the above stuff into variables and playing with them..BUT I WANTED TO DO IN SINGLE QUERY ...Is that POSSIBLE..

thanks in advance..
Logic4fun

 

[PruSQLer]

The easiest way I could come up with involves the use of a Table Expression (&quot;with temp&quot; below). Using subqueries got kind of messy. Hope this helps.

With temp
(count1, count2, sum1,sum2)
As (
Select (Select count(*)
from table1
where Ctype = 0)
, (Select count(*)
From table1
Where Ctype = 1)
, sum(calltime)
, sum(holdtime)
From table1

)
Select count1, count2
, sum1
, sum2
, sum1 / count1
, sum2 / count1
From temp