Adding a percentage to a Top 5 query

  • Thread starter Thread starter red6000
  • Start date Start date
R

red6000

Hi I have the following Query:

SELECT TOP 5 QuestionData.CSRName, Count(QuestionData.CSRName) AS
Number_of_CSRName,
FROM QuestionData
GROUP BY QuestionData.CSRName
ORDER BY Count(QuestionData.CSRName) DESC;

What I would like is a 2nd column that shows the percentage of the record vs
the total. IE if out of 100 records I would like my Top 5 query to produce:

CSRName Number_of_CSR Name Percentage_of-Total
Person8 25 25%
Person1 16 16%
Person2 15 15%
Person12 13 13%
Person11 10 10%

How can I do this?

Thanks
 
Red,

With most query issues, I find a Divide and Conquer strategy often useful.

Create a query that totals the total number of records, counting on the name
or the primary key. Then create a third query that uses the new query and
your existing query, creating a calculated field for the percentage. In the
SQL below, your original query is named "QuestionDataQuery". The 2nd query
is called "QD2":

QuestionDataQuery SQL (Removed unnecessary comma from your post)
--------------------------------------------------------------------------------------------
SELECT TOP 5 QuestionData.CSRName, Count(QuestionData.CSRName) AS
Number_of_CSRName
FROM QuestionData
GROUP BY QuestionData.CSRName
ORDER BY Count(QuestionData.CSRName) DESC;

QD2 Query SQL
---------------------------------------------------------------------------------------------
SELECT Count(QuestionData.CSRName) AS CountOfCSRName
FROM QuestionData;

Final Query SQL
----------------------------------------------------------------------------------------------
SELECT QuestionDataQuery.CSRName, QuestionDataQuery.Number_of_CSRName,
[Number_of_CSRName]/[QD2].[CountOFCSRName] AS PercentageOfTotal
FROM QuestionDataQuery, QD2

Then format PercentageOfTotal as Percent in your form or report.

Hope that helps.
Sprinks
 
Back
Top