Welcome to SPN

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

Sign Up Now!
  1.   Become a Supporter    ::   Make a Contribution   
    Target (Recurring Monthly): $200 :: Achieved: $95

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
    >
    >
    >
     
Since you're here... we have a small favor to ask...

More people are visiting & reading SPN than ever but far fewer are paying to sustain it. Advertising revenues across the online media have fallen fast. So you can see why we need to ask for your help. Donating to SPN's is vote for free speech, for diversity of opinions, for the right of the people to stand up to religious bigotry. Without any affiliation to any organization, this constant struggle takes a lot of hard work to sustain as we entirely depend on the contributions of our esteemed writers/readers. We do it because we believe our perspective matters – because it might well be your perspective, too... Fund our efforts and together we can keep the world informed about the real Sikh Sikhi Sikhism. If everyone who writes or reads our content, who likes it, helps us to pay for it, our future would be much more secure. Every Contribution Matters, Contribute Generously!

    Become a Supporter      ::     Make a Contribution     



Share This Page