Select top 10 dialled numbers

[stepone]

Hi All,

I have been stumped by this top 10 query. I have a table of phone calls. The critical fields are ;

Handset (the mobile number being used to make the call)
DialledNumber
TotalSeconds (number of seconds the call lasted).

I want to write a query which will give me, for each handset, the top 5 dialled numbers in terms of total seconds.

I group the table first to get Handset,DialledNumber,SUM(TotalSeconds). Call this QUERY1

Then I do a second query on QUERY 1 where the criteria on SumOFTotalSeconds is as follows ;

In (SELECT TOP 10 [DialledNumber]
FROM tblMasterCalls WHERE [tblMasterCalls].[HandsetNumber] = [HandsetNumber]
GROUP BY tblMasterCalls.CleanDialledNumber
ORDER BY Sum(tblMasterCalls.TotalSeconds) DESC;)

But this gives me all calls to the overall top 10 dialled numbers - NOT the top 10 per handset.

Can anyone help me with this ?

Thanks,
StepOne

 

[llkhoutx]

I don't think that the "In(..)" does anything for your. Merely query a query. That's lightning fast.

 

[stepone]

Hi llkhoutx,

You can't query a query to get the top 10 per handset. Unless you can show me a way of doing this ?

Thanks,
StepOne

 

[RogerCooper]

You can't do this with a single query. You will need to either have a query for each handset, or create table and walk through this with code.

 

[jdraw]

I created a table based on some mocked up raw data.

Table HandsetInfo is

ID	Handset	DialledNumber	CallLengthSeconds
1	1	2345678762	345
2	1	1234567890	100
3	1	3232323232	58
4	2	4354678667	317
5	2	2343212344	200
6	3	2736492918	128
7	3	6758443820	21
8	3	8483828880	48
9	3	9838475960	325
10	3	2736492718	123
11	3	6758482120	20
12	3	8483828180	48
13	3	9838475960	325
14	3	2736492198	115
15	3	6758482520	20
16	3	8483828530	48
17	3	9838475260	325
18	1	3232363232	98
19	1	2345678762	126
20	1	1234567890	180

This query was used to create the table Handset from the raw data in HandsetInfo

SELECT HandsetInfo.Handset, HandsetInfo.DialledNumber
, Sum(HandsetInfo.[CallLengthSeconds]) AS TotSec INTO Handset
FROM HandsetInfo
GROUP BY HandsetInfo.Handset, HandsetInfo.DialledNumber;

I summed the TotalSeconds where there were multiple calls from the same handset to the same dialed number and placed these in a new table.
The new table Handset has fields
Handset,DialledNumber, TotSec

Handset table

Handset	DialledNumber	TotSec
1	1234567890	280
1	2345678762	471
1	3232323232	58
1	3232363232	98
2	2343212344	200
2	4354678667	317
3	2736492198	115
3	2736492718	123
3	2736492918	128
3	6758443820	21
3	6758482120	36
3	6758482520	20
3	8483828180	49
3	8483828530	42
3	8483828880	78
3	9838475260	325
3	9838475960	650

To get the top 10 dialled numbers based on TotSec from each handset

SELECT A.Handset, A.DialledNumber, A.TotSec
FROM Handset AS A
WHERE A.TotSec in 
    (Select top 10 b.totsec from handset as B where
A.handset = b.handset   order by b.totsec desc)
GROUP BY A.Handset, A.DialledNumber, TotSec
ORDER BY handset, totsec DESC;

Result is:

Handset	DialledNumber	TotSec
1	2345678762	471
1	1234567890	280
1	3232363232	98
1	3232323232	58
2	4354678667	317
2	2343212344	200
3	9838475960	650
3	9838475260	325
3	2736492918	128
3	2736492718	123
3	2736492198	115
3	8483828880	78
3	8483828180	49
3	8483828530	42
3	6758482120	36
3	6758443820	21

 

[llkhoutx]

Jdraw's solution is incorrect, that is, the SQL ORDER if incorrect, it should be "ORDER BY totsec DESC".

 

[jdraw]

llkhoutx,
I think it depends on what the OP wants.
If I remove handset from the Order BY, I get

Handset	DialledNumber	TotSec
3	9838475960	650
1	2345678762	471
3	9838475260	325
2	4354678667	317
1	1234567890	280
2	2343212344	200
3	2736492918	128
3	2736492718	123
3	2736492198	115
1	3232363232	98
3	8483828880	78
1	3232323232	58
3	8483828180	49
3	8483828530	42
3	6758482120	36
3	6758443820	21

As I understood things, the OP wanted top 10 calls by handset based on totalSeconds.

Perhaps the OP could tell us more. He/she says 5 in one spot and 10 in another??

 

[stepone]

Hi jdraw,

Thanks, your solution will work, although I was trying to do it without creating a table as a middle step. Would it not be possible just to use a SELECT query as the middle step and run your second query on the first ? I will give that a go, although I assume for some reason it won't work, otherwise you would have suggested it .

Sorry about the confusion over top 10/ top 5. The actual report I am writing is looking for top 10, although the logic will work the same either way.

Thanks again,
StepOne

 

[jdraw]

If you don't want to create a table, you could create a query such as

SELECT HandsetInfo.Handset, HandsetInfo.DialledNumber, Sum(HandsetInfo.CallLengthSeconds) AS TotSec
FROM HandsetInfo
GROUP BY HandsetInfo.Handset, HandsetInfo.DialledNumber
ORDER BY HandsetInfo.Handset, Sum(HandsetInfo.CallLengthSeconds) DESC;

and save it. I used vwHandsett

Then use the saved query to get the top 10

SELECT a.Handset,  a.DialledNumber, a.TotSec
FROM vwHandSett as A 
where  a.totsec in
 (Select top 10  b.totsec from vwhandsett as B 
  where
  A.handset = b.handset   
  order by b.totsec desc)