Link to home
Start Free TrialLog in
Avatar of Alansherwen
AlansherwenFlag for United Kingdom of Great Britain and Northern Ireland

asked on

Access query to add up most frequent calls

I have a query to look through a table and bring back the top 30; Telephone number, Destination, Number of Calls, and Total Cost of the calls.  This works fine on one table but another table (same apart from free call records are ommited) it brings back duplicate rows for telephone numbers (different counts).  Also the counts added together do not equal the actual number of calls.

SQL query below:

SELECT TOP 30 CDR_nf.Telno, CDR_nf.Destination, Count(CDR_nf.TelNo) AS CountOfTelNo, Sum(CDR_nf.cost) AS SumofCost
FROM TelNumbers INNER JOIN CDR_nf ON TelNumbers.Number = CDR_nf.CalledFrom
GROUP BY CDR_nf.Telno, CDR_nf.Destination, CDR_nf.cost
ORDER BY Count(CDR_nf.TelNo) DESC;

I cannot see what I have done wrong here and any hel would be appreciated.

Attached is outut of the SQL (I have blocked out the first part of the telephone numbers and highlighted some duplicates. User generated image
ASKER CERTIFIED SOLUTION
Avatar of borki
borki
Flag of Australia image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of Alansherwen

ASKER

Thanks for that, fixed it straight away.

Many thanks.

Alan