Total with a group by query

[mattbrem]

I have a query that takes in two dates, start and end, and a team as parameters. It calculates average interview times, and total interviews grouping by each person in that team. I would like to add a column at the end that shows the total number of interviews done by the entire team.

Output as it is now...

Name............Average Time..........Interviews
Matthew............30.........................7
Jane..................29.........................11

Output I would like

Name............Average Time Interviews Team Interviews
Matthew..........30...............7....................67
Jane................29..............11...................67

Here is the sql as I have it now,

SELECT tblInterviews.[Revenue Specialist], Avg(tblInterviews.[Interview Length]) AS [AvgOfInterview Length], Count(tblInterviews.[Interview Length]) AS [CountOfInterview Length]

FROM tblClient INNER JOIN tblInterviews ON tblClient.VisitID = tblInterviews.VisitID

WHERE (((tblClient.Date) Between [start?] And [End?]) AND ((tblInterviews.[Time End]) Is Not Null))

GROUP BY tblInterviews.[Revenue Specialist], tblInterviews.Subprocess
HAVING (((tblInterviews.[Revenue Specialist]) Is Not Null) AND ((tblInterviews.Subprocess)=[Team?]) AND ((Avg(tblInterviews.[Interview Length])) Is Not Null));

Any suggestions on how I could add a total number of interviews field?

 

[Brianwarnock]

Add a field team_total:IIf(tblInterviews.Subprocess=1 and sum it.

Brian

 

[mattbrem]

It says the expression is typed incorrectly, or is too complex to be evaluated.

Here is the sql I used, did I put the field in wrong?

SELECT tblInterviews.[Revenue Specialist], Avg(tblInterviews.[Interview Length]) AS [AvgOfInterview Length], Count(tblInterviews.[Interview Length]) AS [CountOfInterview Length], Sum(IIf(tblInterviews.Subprocess=1,1,0)) AS team_total

FROM tblClient INNER JOIN tblInterviews ON tblClient.VisitID = tblInterviews.VisitID

WHERE (((tblClient.Date) Between [start?] And [End?]) AND ((tblInterviews.[Time End]) Is Not Null) AND ((tblInterviews.Subprocess)=[Team?]))

GROUP BY tblInterviews.[Revenue Specialist]

HAVING (((tblInterviews.[Revenue Specialist]) Is Not Null) AND ((Avg(tblInterviews.[Interview Length])) Is Not Null));

 

[Brianwarnock]

Sum(IIf(tblInterviews.Subprocess=1,1,0)) AS team_total

I didn't say do that, I had no IIF just Sum(1) as Team_Total as SQL I explained it for design grid before.

You are doing the selection in the Having clause.

Brian

 

[mattbrem]

Sorry I am new at this. You want me to put Team_Total : Sum(1) in the qdg? That gives me the total for each person again, not the team total, or am I doing that wrong?

 

[Brianwarnock]

Sorry just realised that you are not grouping across the team. Guess I had a senior moment

Brian

 

[Brianwarnock]

I'd probably do the group count with a separate query and join the 2 queries but it can probably be done using a sub query, actuall I would probably have used a report with a total in the footer.

Brian

 

[mattbrem]

Combining two queries and joining them worked, thanks! I actually had this as a report already, but when I export the report to excel it doesn't copy over any summation information because I was doing the calculations in the report itself rather than the query.