sub select question

[03Explorer]

I want to use one query to generate this type of data from one table.

Phone Number #calls Avg_Duration(sec)
xxx-xxx-xxxx 1,234 12.34
yyy-yyy-yyyy 12,032 9.03
zzz-zzz-zzzz 435 13.45

SELECT Distinct
  c.phoneNumber,
  (SELECT count(distinct ID) FROM call WHERE PhoneNumber = c.PhoneNumber) #calls,
  (SELECT AVG( duration ) FROM call WHERE PhoneNumber = c.PhoneNumber) AVG_Duration(sec)
FROM
  Call c
GROUP BY
  1,
  2,
  3
ORDER BY
  1 ASC

Am I trying to do something completely wrong with my Sub Select statements which call for the table and element in the external Select Statment?

 

[genomon]

The SQL syntax is wrong; please post back the original table design, along with a few sample records.

"Don't be irreplaceable. If you can't be replaced, you can't be promoted."

 

[johnherman]

You also might consider posting in the ANSI-SQL forum forum220 or in the Microsoft, DB2, Oracle, or appropriate database forum for your database SQL.

-------------------------
The trouble with doing something right the first time is that nobody appreciates how difficult it was - Steven Wright

 

[03Explorer]

Call Table
==========
callID:int (key)
duration:int
PhoneNumber:varchar(32)

CallID duration PhoneNumber
1 1.23 xxx-xxx-xxxx
2 3.23 yyy-yyy-yyyy
3 2.10 zzz-zzz-zzzz
4 2.23 xxx-xxx-xxxx
5 4.32 yyy-yyy-yyyy
6 8.02 xxx-xxx-xxxx
7 1.20 xxx-xxx-xxxx

Looking for output like:

NumberCalled #calls Average_Duration
============ ====== ================
xxx-xxx-xxxx 4 3.17
yyy-yyy-yyyy 2 3.78
zzz-zzz-zzzz 1 2.10

 

[jrbarnett]

Something like:

SELECT PhoneNumber,
 (SELECT Count (*) FROM calls c1 where c1.PhoneNumber = calls.PhoneNumber) As NumCalls,
 SELECT Avg (Duration) FROM calls c2 where c2.PhoneNumber = calls.PhoneNumber As AverageDuration
FROM Calls
GROUP BY PhoneNumber
ORDER BY PhoneNumber

As Genomon and Johnherman say, you don't specify which database engine you are using, so we can't provide an exact response.

John

 

[JarlH]

Is there something I don't understand here, but isn't this just a basic GROUP BY query???
[tt]
SELECT phoneNumber,
COUNT(*) AS #calls,
AVG(duration) AS AVG_Duration
FROM
Call
GROUP BY
phoneNumber
ORDER BY
phoneNumber[/tt]

BTW, CALL is a reserved word in standard SQL. To be avoid future problems double quote it ([tt]"CALL"[/tt]), or rename the table.