Welcome to SPN

Register and Join the most happening forum of Sikh community & intellectuals from around the world.

Sign Up Now!

Adding a percentage to a Top 5 query

Discussion in 'Information Technology' started by red6000, Jul 28, 2006.

  1. red6000

    red6000
    Expand Collapse
    Guest

    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
     
  2. Loading...

    Similar Threads Forum Date
    Sikhs Declining As A Percentage Of India's Population Hard Talk Apr 18, 2016
    Declining Sikh population percentage in Punjab Sikh Sikhi Sikhism Aug 26, 2015
    India Poll percentage surges further, 78.67 per cent in Punjab Breaking News Feb 1, 2012
    Sikh News Percentage of science students low in Punjab: Minister (New Kerala) Breaking News Mar 13, 2008

  3. Sprinks

    Sprinks
    Expand Collapse
    Guest

    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

    "red6000" wrote:

    > 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
    >
    >
    >
     

Share This Page