Query - Count of Records (duplicate ID)

[Al]

Hi,

Is it possible to do the following:

I need to be able to count the unique number of people who've called within
a given year/month who've only called once and also count unique number
who've called more than once, by Customer Type.

So for example I'd like the output to look like the following (Note:
Customer Type can only contain two options of Internal or External):

Year Month Customer Type # Called once # Call > Once
2008 10 External 180 15
2008 10 Internal 79 11

tblRemedy
- CaseID
- CallerID
- CreateMonth
- CreateYear
- CustomerType

Thanks in advance.

Al.

 

[Dale Fye]

Yes. Try:

SELECT T.CreateYear, T.CreateMonth, T.CustomerType,
Sum(IIF([T.Calls] = 1, 1, 0)) as [# Called Once],
Sum(IIF([T.Calls] > 1, 1, 0)) as [# Call > Once]
FROM (SELECT CreateYear, CreateMonth, CallerID, CustomerType,
Count(CaseID) as Calls
FROM tblRemedy
GROUP BY CreateYear, CreateMonth, CallerID, CustomerType
) as T
GROUP BY T.CreateYear, T.CreateMonth, T.CustomerType

The sub-query in this query determines the number of calls per Customer, per
year/month/customerType. The outer portion then uses the Sum(IIF(exp, true,
false)) expression to determine whether to count each record, and how to
count it.

-----
HTH
Dale

email address is invalid
Please reply to newsgroup only.

 

[Al]

Perfect, exactly what I need.

Thanks for your help on this - appreciated.

Al.

Yes. Try:

SELECT T.CreateYear, T.CreateMonth, T.CustomerType,
Sum(IIF([T.Calls] = 1, 1, 0)) as [# Called Once],
Sum(IIF([T.Calls] > 1, 1, 0)) as [# Call > Once]
FROM (SELECT CreateYear, CreateMonth, CallerID, CustomerType,
Count(CaseID) as Calls
FROM tblRemedy
GROUP BY CreateYear, CreateMonth, CallerID, CustomerType
) as T
GROUP BY T.CreateYear, T.CreateMonth, T.CustomerType

The sub-query in this query determines the number of calls per Customer, per
year/month/customerType. The outer portion then uses the Sum(IIF(exp, true,
false)) expression to determine whether to count each record, and how to
count it.

-----
HTH
Dale

email address is invalid
Please reply to newsgroup only.

Hi,

Is it possible to do the following:

I need to be able to count the unique number of people who've called within
a given year/month who've only called once and also count unique number
who've called more than once, by Customer Type.

So for example I'd like the output to look like the following (Note:
Customer Type can only contain two options of Internal or External):

Year Month Customer Type # Called once # Call > Once
2008 10 External 180 15
2008 10 Internal 79 11

tblRemedy
- CaseID
- CallerID
- CreateMonth
- CreateYear
- CustomerType

Thanks in advance.

Al.