• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 365
  • Last Modified:

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. Screenshot of SQL output
0
Alansherwen
Asked:
Alansherwen
1 Solution
 
borkiCommented:
Why are you using

CDR_nf.cost

in the GROUP BY clause? You wo't need it there, as you are using an aggregate on this column.
0
 
AlansherwenAuthor Commented:
Thanks for that, fixed it straight away.

Many thanks.

Alan
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Improve Your Query Performance Tuning

In this FREE six-day email course, you'll learn from Janis Griffin, Database Performance Evangelist. She'll teach 12 steps that you can use to optimize your queries as much as possible and see measurable results in your work. Get started today!

Tackle projects and never again get stuck behind a technical roadblock.
Join Now