Counting within a count group by query?

[elvio.serrao]

Hi All,

I would like to ask for your assistance with merging two queries into
one to achieve a desired outcome. The first query counts the total
occurrences for each ANI. The second query counts the total for a
specific condition of each ANI.

As an example the raw data is:

Date Time FinalStatus SysID
DNIS ANI
10/05/2007 11:26:33 AM 30 7015
80675901 255328862
10/05/2007 11:26:26 AM 30 7025
85867801 255328862
10/05/2007 11:26:23 AM 30 7025
85867001 255632043
10/05/2007 11:26:24 AM 30 7025
85867801 338841575
10/05/2007 11:26:35 AM 37 7025
85867001 394857311
10/05/2007 11:26:35 AM 30 7015
80675901 438162610
10/05/2007 11:26:30 AM 30 7015
80675401 632753715
10/05/2007 11:26:36 AM 30 7025
85867001 632753715
10/05/2007 11:26:33 AM 30 7025
85867801 632753715
10/05/2007 11:26:28 AM 37 7025
85867801 832733914
10/05/2007 11:26:35 AM 30 7025
85867001 932533083
10/05/2007 11:26:28 AM 30 7015
80675401 932832500
10/05/2007 11:26:34 AM 30 7015
80675901 933453048
10/05/2007 11:26:34 AM 30 7015
80675901 935117655
10/05/2007 11:26:23 AM 30 7015
80675901 941625218
10/05/2007 11:26:25 AM 30 7025
85867801 941625218
10/05/2007 11:26:25 AM 37 7025
85867801 941625218
10/05/2007 11:26:31 AM 30 7025
85867801 941625218
10/05/2007 11:26:30 AM 30 7025
85867801 945769483
10/05/2007 11:26:33 AM 30 7025
85867001 947765881
10/05/2007 11:26:30 AM 30 7015
80675401 955174778
10/05/2007 11:26:28 AM 30 7015
80675901 955791158
10/05/2007 11:26:26 AM 30 7025
85867001 955791158
10/05/2007 11:26:32 AM 30 7025
85867801 955791158
10/05/2007 11:26:34 AM 37 7025
85867001 955791158


The expected outcome will be:

ANI TotalCalls FailedCalls
255328862 2 0
255632043 1 0
338841575 1 0
394857311 1 1
438162610 1 0
632753715 3 0
832733914 1 1
932533083 1 0
932832500 1 0
933453048 1 0
935117655 1 0
941625218 4 1
945769483 1 0
947765881 1 0
955174778 1 0
955791158 4 1

The two queries that can acheive the required data results are:

SELECT LoggedCalls.ANI, Count(*) AS TotalCalls
FROM LoggedCalls
WHERE Right(DNIS,2)="01"
GROUP BY LoggedCalls.ANI
ORDER BY Count(*) DESC;

SELECT LoggedCalls.ANI, COUNT(*) AS FailedCalls
FROM LoggedCalls
WHERE LoggedCalls.FinalStatus<>'30' And Right(LoggedCalls.DNIS,
2)="01"
GROUP BY LoggedCalls.ANI
ORDER BY COUNT(*) DESC;

I don't know how to combine the two queries to acheive the expected
outcome above. Any help is greatly appreciated.

Thank you.

 

[Andy Hull]

Hi

You can do this as a single query...

SELECT ANI, SUM(1) AS TotalCalls, SUM(IIF(FinalStatus <> '30', 1, 0)) AS
FailedCalls
FROM LoggedCalls
WHERE RIGHT(DNIS,2) = "01"
GROUP BY ANI


Regards

Andy Hull

 

[elvio.serrao]

Worked beautifully. Thank you very much for your help.

I want to take it one final step, which I'm very close to. I've added
another column to the view, which adds the percentile success rate.
The only issue I'm experiencing is I'm unable to ORDER BY the
SuccessRate. If I try, I receive an input prompt for TotalCalls. I'd
like to know how to fix it and understand why the input prompt is
occurring.

Current Query:
SELECT LoggedCalls.ANI, Sum(1) AS TotalCalls,
Sum(IIf([FinalStatus]<>'30',1,0)) AS FailedCalls, Format$
((ROUND((TotalCalls-FailedCalls)/TotalCalls,4)),"Percent") AS
SuccessRate
FROM LoggedCalls
WHERE (((Right([DNIS],2))="01"))
GROUP BY LoggedCalls.ANI
ORDER BY Sum(IIf([FinalStatus]<>'30',1,0)) DESC;

Preference is Ascending to ORDER BY Format$((ROUND((TotalCalls-
FailedCalls)/TotalCalls,4)),"Percent")

 

[Andy Hull]

Hi again

You can't use the names of calculations in the order by clause.
So you will have to repeat the calculations so the order by looks like...

ORDER BY ROUND((Sum(1)-Sum(IIf([FinalStatus]<>'30',1,0)))/Sum(1),4)

Note: I have also removed the format so that the values are sorted
numerically.
If you dont then they will be sorted as text and 100% will appear before 11%.
But do leave the format in the SELECT

Regards

Andy Hull

Worked beautifully. Thank you very much for your help.

I want to take it one final step, which I'm very close to. I've added
another column to the view, which adds the percentile success rate.
The only issue I'm experiencing is I'm unable to ORDER BY the
SuccessRate. If I try, I receive an input prompt for TotalCalls. I'd
like to know how to fix it and understand why the input prompt is
occurring.

Current Query:
SELECT LoggedCalls.ANI, Sum(1) AS TotalCalls,
Sum(IIf([FinalStatus]<>'30',1,0)) AS FailedCalls, Format$
((ROUND((TotalCalls-FailedCalls)/TotalCalls,4)),"Percent") AS
SuccessRate
FROM LoggedCalls
WHERE (((Right([DNIS],2))="01"))
GROUP BY LoggedCalls.ANI
ORDER BY Sum(IIf([FinalStatus]<>'30',1,0)) DESC;

Preference is Ascending to ORDER BY Format$((ROUND((TotalCalls-
FailedCalls)/TotalCalls,4)),"Percent")

Hi

You can do this as a single query...

SELECT ANI, SUM(1) AS TotalCalls, SUM(IIF(FinalStatus <> '30', 1, 0)) AS
FailedCalls
FROM LoggedCalls
WHERE RIGHT(DNIS,2) = "01"
GROUP BY ANI

Regards

Andy Hull















- Show quoted text -

 

[John Spencer]

Try the following

ORDER BY ((ROUND((Sum(*)-FailedCalls)/SUM(*),4)),"Percent")

I suspect that the ORDER BY clause does not know what TotalCalls is your
expression.
--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

Worked beautifully. Thank you very much for your help.

I want to take it one final step, which I'm very close to. I've added
another column to the view, which adds the percentile success rate.
The only issue I'm experiencing is I'm unable to ORDER BY the
SuccessRate. If I try, I receive an input prompt for TotalCalls. I'd
like to know how to fix it and understand why the input prompt is
occurring.

Current Query:
SELECT LoggedCalls.ANI, Sum(1) AS TotalCalls,
Sum(IIf([FinalStatus]<>'30',1,0)) AS FailedCalls, Format$
((ROUND((TotalCalls-FailedCalls)/TotalCalls,4)),"Percent") AS
SuccessRate
FROM LoggedCalls
WHERE (((Right([DNIS],2))="01"))
GROUP BY LoggedCalls.ANI
ORDER BY Sum(IIf([FinalStatus]<>'30',1,0)) DESC;

Preference is Ascending to ORDER BY Format$((ROUND((TotalCalls-
FailedCalls)/TotalCalls,4)),"Percent")

Hi

You can do this as a single query...

SELECT ANI, SUM(1) AS TotalCalls, SUM(IIF(FinalStatus <> '30', 1, 0)) AS
FailedCalls
FROM LoggedCalls
WHERE RIGHT(DNIS,2) = "01"
GROUP BY ANI

Regards

Andy Hull















- Show quoted text -