Post reply

SQL help with total percentage

  • December 2, 2008 at 11:08 am

    #126177

    I have a table from which i need to calculate the following

    - get total percentage where col2 is either 'a', or 'b' or 'c' .

    - do the above only where col1 = 'specific'

    select (convert(numeric(5,2),count(col2))/(Select convert(numeric(5,2),count(col2)) from Table1)) * 100

    AS "Percentage Of Total "

    from table1

    where col1 = 'specific'

    and col2 like '%a%'

    Here both col1 and col2 are varchar so using like will ldefinitely have some perf impact but is there any other way we could do the search. col2 has data as 'abkl' or 'bcdfg' and so on, each letter represents some code definition and so i need to calculate the total percentage for each code definition. any feedback or help on this will be greatly appreciated.

    TIA

  • December 2, 2008 at 11:11 am

    #906402

    Hi Ishaan

    Can you please provide some sample data, and an expected result set, as in the link below?

    Cheers

    ChrisM

    “Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw

    For fast, accurate and documented assistance in answering your questions, please read this article.
    Understanding and using APPLY, (I) and (II) Paul White
    Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

  • December 2, 2008 at 11:53 am

    #906432

    Sorry Chris, heres some more details wrt data and table structure

    create table Table1

    col1 varchar(10),

    col2 varchar(25))

    Data looks like

    Col1Col2

    specific ABCD

    specific-1BDOP

    specific GHFE

    specific BCDE

    specific-2UIOP

    specific GHJK

    output

    Col1A%B%C%D%E%F%G%H%I%J%K%O%P%

    specific16%50%33%50%33%16%33%33%16%16%16%33%33%

    Here i have to get only records which has col1 = 'specific'

    but for col2 i need a total percentage of how much A, B,C or any other code is used.

    Thanks

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply