Aggregate Query

[Danyluk]

I have certain tables as follows:

UserID UserName CallDT ResultCode

I am trying to run a query that will show me the total amount of calls for "UserID"

This is what I have done.

SELECT     UserID, Sum(CAST(UserID as Int))AS 'Total',  UserName, CallDT, ResultCode
FROM         CallHist
WHERE     (CallDT BETWEEN '2010-08-17' AND '2010-08-18') AND (NOT (UserID = N'PDIALER'))
GROUP BY UserID, UserName, CallDT, ResultCode

The problem is that it doesn't show me the total for an agent. This is what it gives me.

UserID | Total | UserName | CallDT | ResultCode
2005 |2005 |Craig Costello|2010-08-17 | 80
2005 |2005 |Craig Costello|2010-08-17 | 45
2046 |2046 |Joe Servantez| 2010-08-17 | 80
2046 |2046 |Joe Servantez| 2010-08-17 | 71
2046 |2046 |Joe Servantez| 2010-08-17 | 71
2086 |2086 |Vic Day | 2010-08-17 | 85
2086 |2086 |Vic Day | 2010-08-17 | 7
2086 |2086 |Vic Day | 2010-08-17 | 71

What I want to accomplish is the following:

UserID | Total | Username | CallDT | RC80 |RC45|
2005 |---2---| Craig C. | 2010-08-17|---1---|--1-|

So basically UserID who is Username made a total of 2 calls on 2010-08-17 and out of those 2 calls 1 was from ResultCode 80 and the other from ResultCode 45

Could someone please help me with this. I am losing my mind.

Thank you,

[Oralloy]

Well, there's two ways - use SUM(1) or COUNT(*)

Either should work.

Good luck.

Cheers!

[colintis]

SQL cannot get your last requirements as it will not showing multiple columns from the same field. Your best approach for this will be:

UserID | Total | UserName | CallDT | ResultCode
2005 |1 |Craig Costello|2010-08-17 | 80
2005 |1 |Craig Costello|2010-08-17 | 45
2046 |1 |Joe Servantez| 2010-08-17 | 80
2046 |2 |Joe Servantez| 2010-08-17 | 71
2086 |1 |Vic Day | 2010-08-17 | 85
2086 |1 |Vic Day | 2010-08-17 | 7
2086 |1 |Vic Day | 2010-08-17 | 71

With this:

SELECT     UserID, Count(DISTINCT UserID)AS 'Total',  UserName, CallDT, ResultCode
FROM         CallHist
WHERE     (CallDT BETWEEN '2010-08-17' AND '2010-08-18') AND (NOT (UserID = N'PDIALER'))
GROUP BY UserID, UserName, CallDT, ResultCode

[Danyluk]

colintis,
I tried what you said and it gives the same result as the one that I did.

Do you have any other suggestions?

[Oralloy]

Ok, now I understand. You need to use a TRANSFORM query something like this:

TRANSFORM COUNT(*) AS 'Total'
SELECT  COUNT(*),  UserName, CallDT, ResultCode 
  FROM  CallHist
  WHERE  (CallDT BETWEEN '2010-08-17' AND '2010-08-18')
         AND (UserID <> N'PDIALER')
  GROUP BY UserID, UserName, CallDT, ResultCode
PIVOT UserName

Outside of that, it's been a while since I've done such a thing. They're usually called 'Pivot Tables', or 'Pivot Queries', if you need to google them.

Try this link for some useful pages:

http://www.google.com/#hl=en&q=sql+transform+query&aq=4m&aqi=g4g-m3&aql=&oq=TRANSFORM+QUERY

[colintis]

A direct reference which I think it explains more clearly on
TRANSFORM QUERY

[Danyluk]

Well it seems like I am getting there. This is what I have now:

TRANSFORM Count(callhist.userid) AS total
SELECT callhist.userid, callhist.username, callhist.resultcode
FROM callhist
WHERE (((callhist.calldt)>=[Enter Start date] And (callhist.calldt)<=[Enter End Date]))
GROUP BY callhist.userid, callhist.username, callhist.resultcode
PIVOT callhist.resultcode;

I want to say thank you for the helpful links this is going to be useful but the problem I am having now is that in the "WHERE" statement I am getting this error:

"Microsoft Access database engine does not recognize '[Enter Start date]' as a valid field name or expression"

I use this same statement in other queries but the only difference is I have it identified in the "ORDER BY" statement at the end.

Can you tell me why I am getting this error?

[colintis]

Give a try with referencing the form address such as

[forms]![<your form>]![Enter Start Date]

a reference of a similar case

[Oralloy]

@Danyluk,

I think you need to have a parameter bound to your query. Unfortunately I'm not the Access guy to answer that question for you directly.

Are you familiar with using DAO to execute SQL against your DB?

Also, I think that the COUNT value will always be 1 in your query there, but I'm not sure.

Luck!