Duplicate records Help

[MadMax7]

I hope somebody can help, i have a table that has a number of fields but for this query i only need 4 fields, they are

Date CLI Less60Flag Greater60Flag

The problem i have is the table contains the number that a customer contacted us on (CLI), what i need to do is count the total number of calls a customer made where the call is less than 60 sec (less60Flag) and call is greater than 300 Sec(Greater60Flag)this is easy with a normal select query but the problem is i need to count the number of times the same customer called more than once.

Hope this makes sense

 

[ddiamond]

what i need to do is count the total number of calls a customer made...

i need to count the number of times the same customer called more than once.

Are you talking about two different queries, or one query with the criteria above? If we are talking about one query, I do not not understand how the two quotes above fit together. Perhaps an example would help.

 

[MadMax7]

sorry my a example of the some of the data is


Date CLI Less60Flag Greater60Flag
01/03/06 01619231234 1
01/03/06 01619231234 1
01/03/06 01619234567 1

so for the above data i need to show that we had a total of 3 calls for the day and 1 multiple call.

Is there a way i could show this in one query or would i have to count the number of total calls in one query and then the number of multiple calls in another query

 

[PHV]

Something like this ?
SELECT [Date], CLI, Count(*) As TotalCalls
, Sum(Less60Flag) As TotalLess60
, Sum(Greater60Flag) As TotalGreater60
FROM yourTable
GROUP BY [Date], CLI

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[ddiamond]

so for the above data i need to show that we had a total of 3 calls for the day..."

SELECT [Date], Count(DATE) As TotalCalls
FROM yourTable
GROUP BY [Date]

"so for the above data i need to show that we had ... 1 multiple call."

First create a subquery:

SELECT [Date], CLI, Count(CLI) As MultipleCalls
FROM yourTable
GROUP BY [Date], CLI
HAVING COUNT(CLI)>1

Then create a query that uses that subquery

SELECT [DATE], Count([Date]) As TotalMultCalls
FROM subquery
Group by [Date]

 

[ddiamond]

A slight modification to PHV's solution:
SELECT [Date], Count(*) As TotalCalls
, Sum(nz(Less60Flag,0)) As TotalLess60
, Sum(nz(Greater60Flag,0)) As TotalGreater60
FROM yourTable
GROUP BY [Date]

 

[lespaul]

I don't think you need the NZ function.

Leslie

Anything worth doing is a lot more difficult than it's worth - Unknown Induhvidual

Essential reading for anyone working with databases: The Fundamentals of Relational Database Design