Unanswered: SQL Help - combining tables and Count Distinct
I'm new to SQL and I'm learning as I go so forgive me if i'm asking a stupid question but I'm currently using a database for quality monitoring and there are 3 separate tables, each of them have their own marking criteria etc.
Regardless of the differences between the 3 tables marking criteria they do however all share the same GRADE choices at the end, I'm now trying to create a report which combines all 3x tables results and counts the Grades etc.
I need to incorporate another column that counts the Unique advisors. I know this SQL works if i put it in a separate query:
SELECT Count(*) AS [UNIQUE ADVISORS]
(SELECT DISTINCT AdvisorName FROM CalcCarelineRQM) AS T;
but I don't know how to mix a SELECT DISTINCT inside of the rest of the SQL (below)
This SQL works for the CalcCarelineRQM table, but i now don't know how I can combine the other tables results into the same query, preferably keeping the same Grouping level?
My 3 tables:
My SQL so far:
Round(Avg([CalcCarelineRQM].Percentage),2) AS [AVRGE CARELINE SCORE],
Count(CalcCarelineRQM.UserID) AS [VOL CALLS MONTD],
Sum(IIf(([CalcCarelineRQM].Classification)="Excellent Customer Service",1,0)) AS [EXCELLENT CUS SERV],
Sum(IIf(([CalcCarelineRQM].Classification)="Good Customer Service",1,0)) AS [GOOD CUS SERV],
Sum(IIf(([CalcCarelineRQM].Classification)="Customer Service Needs Improvement",1,0)) AS [CUS SERV NEEDS IMPRMNT],
Sum(IIf(([CalcCarelineRQM].Classification)="Business Rules Need Improvement",1,0)) AS [BUS RULE REQS IMPRMNT],
Sum(IIf(([CalcCarelineRQM].Classification)="Customer Service is Poor (not a 1st time fix)",1,0)) AS [POOR CUS SERV],
Sum(IIf(([CalcCarelineRQM].FirstTimeFix)="YES",1,0)) AS [FTF YES],
Sum(IIf(([CalcCarelineRQM].FirstTimeFix)="NO",1,0)) AS [FTF NO],
Round([FTF YES]/([FTF YES]+[FTF NO]),4)*100 AS [FTF %]
GROUP BY CalcCarelineRQM.TeamManagerName;
Ok so I'm not sure that I completely understand what you are asking but I'm going to take a stab at it.
I'm guessing that you want to do a similar query on the other 2 tables like you have for the CalcCareLineRQM table. I would probably have 3 separate queries (1 for each table) and then do a UNION to join them all into one result.
If it works in a separate query have you tried adding that query to the first query and use a join between the two? You will need to some how relate the data to the main query otherwise you will get the same number of Advisors for each row that is returned from the main query.