Professional Web Applications Themes

About percentages - Microsoft SQL / MS SQL Server

I have a table as follows: UserID BrowserType 1 Mozilla/4.0 (compatible; MSIE 6.0; Windows NT 5.0) 2 Mozilla/4.0 (compatible; MSIE 6.0; Windows 98) 3 Mozilla/4.0 (compatible; MSIE 6.0; Windows 98) 4 Mozilla/4.0 (compatible; MSIE 5.22; Mac_PowerPC) 5 Mozilla/4.0 (compatible; MSIE 6.0; Windows NT 5.0) etc.. I'd like to get an output as follows: 50% Mozilla/4.0 (compatible; MSIE 6.0; Windows NT 5.0) 22% Mozilla/4.0 (compatible; MSIE 6.0; Windows 98) 28% Mozilla/4.0 (compatible; MSIE 5.22; Mac_PowerPC) How do I write a SELECT statement to do that? Thank you....

  1. #1

    Default About percentages

    I have a table as follows:

    UserID BrowserType
    1 Mozilla/4.0 (compatible; MSIE 6.0; Windows NT 5.0)
    2 Mozilla/4.0 (compatible; MSIE 6.0; Windows 98)
    3 Mozilla/4.0 (compatible; MSIE 6.0; Windows 98)
    4 Mozilla/4.0 (compatible; MSIE 5.22; Mac_PowerPC)
    5 Mozilla/4.0 (compatible; MSIE 6.0; Windows NT 5.0)
    etc..

    I'd like to get an output as follows:

    50% Mozilla/4.0 (compatible; MSIE 6.0; Windows NT 5.0)
    22% Mozilla/4.0 (compatible; MSIE 6.0; Windows 98)
    28% Mozilla/4.0 (compatible; MSIE 5.22; Mac_PowerPC)

    How do I write a SELECT statement to do that?

    Thank you.


    Ed Guest

  2. #2

    Default Re: About percentages

    Try:

    Select
    BrowserType
    , count (*) * 100.0 / (select count (*) from MyTable) as [Percent]
    from
    MyTable
    group by
    BrowserType

    --
    Tom

    ---------------------------------------------------------------
    Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
    SQL Server MVP
    Columnist, SQL Server Professional
    Toronto, ON Canada
    www.pinnaclepublishing.com/sql


    "Ed" <com> wrote in message news:phx.gbl...
    I have a table as follows:

    UserID BrowserType
    1 Mozilla/4.0 (compatible; MSIE 6.0; Windows NT 5.0)
    2 Mozilla/4.0 (compatible; MSIE 6.0; Windows 98)
    3 Mozilla/4.0 (compatible; MSIE 6.0; Windows 98)
    4 Mozilla/4.0 (compatible; MSIE 5.22; Mac_PowerPC)
    5 Mozilla/4.0 (compatible; MSIE 6.0; Windows NT 5.0)
    etc..

    I'd like to get an output as follows:

    50% Mozilla/4.0 (compatible; MSIE 6.0; Windows NT 5.0)
    22% Mozilla/4.0 (compatible; MSIE 6.0; Windows 98)
    28% Mozilla/4.0 (compatible; MSIE 5.22; Mac_PowerPC)

    How do I write a SELECT statement to do that?

    Thank you.



    Tom Guest

  3. #3

    Default Re: About percentages

    Thanks. That works great.

    I want to know if this is possible. Some of the results that I have in
    BrowserType are varying because a user might have installed a new service
    pack, etc. But overall, they are all referring to the same browser and
    platform. So, I would like to combine, say the following, into the same
    category and get their percentages tallied together:

    Mozilla/4.0 (compatible; MSIE 5.5; Windows NT 5.0)
    Mozilla/4.0 (compatible; MSIE 5.5; Windows NT 5.0; T312461)

    Mozilla/4.0 (compatible; MSIE 6.0; Windows NT 5.0; .NET CLR 1.0.3705)
    Mozilla/4.0 (compatible; MSIE 6.0; Windows NT 5.0; T312461)

    Is that possible?


    "Tom Moreau" <spam.me.cips.ca> wrote in message
    news:phx.gbl...
    Try:

    Select
    BrowserType
    , count (*) * 100.0 / (select count (*) from MyTable) as [Percent]
    from
    MyTable
    group by
    BrowserType

    --
    Tom

    ---------------------------------------------------------------
    Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
    SQL Server MVP
    Columnist, SQL Server Professional
    Toronto, ON Canada
    www.pinnaclepublishing.com/sql


    "Ed" <com> wrote in message
    news:phx.gbl...
    I have a table as follows:

    UserID BrowserType
    1 Mozilla/4.0 (compatible; MSIE 6.0; Windows NT 5.0)
    2 Mozilla/4.0 (compatible; MSIE 6.0; Windows 98)
    3 Mozilla/4.0 (compatible; MSIE 6.0; Windows 98)
    4 Mozilla/4.0 (compatible; MSIE 5.22; Mac_PowerPC)
    5 Mozilla/4.0 (compatible; MSIE 6.0; Windows NT 5.0)
    etc..

    I'd like to get an output as follows:

    50% Mozilla/4.0 (compatible; MSIE 6.0; Windows NT 5.0)
    22% Mozilla/4.0 (compatible; MSIE 6.0; Windows 98)
    28% Mozilla/4.0 (compatible; MSIE 5.22; Mac_PowerPC)

    How do I write a SELECT statement to do that?

    Thank you.


    Ed Guest

  4. #4

    Default Re: About percentages

    In that case you could create a reference table with a column for the
    full name, and another column with the BrowserType. This way you will
    not have to change your query each time a new service pack comes out.

    Select
    BrowserType
    , count (*) * 100.0 / (select count (*) from MyTable) as [Percent]
    from
    MyTable
    inner join BrowserLookup
    on MyTable.FullName = BrowserLookup.FullName
    group by
    BrowserType

    Gert-Jan


    Ed wrote: 
    Gert-Jan Guest

  5. #5

    Default Re: About percentages

    Thanks. Is it possible to get a report so that under each
    browser+percentage, I can see who the users are?

    Sample output:

    Mozilla/4.0 (compatible; MSIE 6.0; Windows NT 5.0) 56%
    UserID 2
    UserID 23
    UserID 49
    ...
    Mozilla/4.0 (compatible; MSIE 5.5; Windows NT 5.0) 23%
    UserID 6
    UserID 7
    UserID 9
    ...




    "Tom Moreau" <spam.me.cips.ca> wrote in message
    news:%23d$phx.gbl...
    You can use the CASE construct of the form:

    select
    case
    when BrowserType like 'Mozilla/4.0 (compatible; MSIE 5.5; Windows NT
    5.0%'
    then Mozilla/4.0 (compatible; MSIE 5.5; Windows NT 5.0)'
    when BrowserType like 'Mozilla/4.0 (compatible; MSIE 6.0; Windows NT
    5.0%'
    then Mozilla/4.0 (compatible; MSIE 6.0; Windows NT 5.0)'
    end as BrowserType
    , count (*) * 100.0 / (select count (*) from MyTable) as [Percent]
    from
    MyTable
    group by
    case
    when BrowserType like 'Mozilla/4.0 (compatible; MSIE 5.5; Windows NT
    5.0%'
    then Mozilla/4.0 (compatible; MSIE 5.5; Windows NT 5.0)'
    when BrowserType like 'Mozilla/4.0 (compatible; MSIE 6.0; Windows NT
    5.0%'
    then Mozilla/4.0 (compatible; MSIE 6.0; Windows NT 5.0)'
    end

    --
    Tom

    ----------------------------------------------------
    Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
    SQL Server MVP
    Columnist, SQL Server Professional
    Toronto, ON Canada
    www.pinnaclepublishing.com/sql
    ..
    "Ed" <com> wrote in message
    news:%phx.gbl...
    Thanks. That works great.

    I want to know if this is possible. Some of the results that I have in
    BrowserType are varying because a user might have installed a new service
    pack, etc. But overall, they are all referring to the same browser and
    platform. So, I would like to combine, say the following, into the same
    category and get their percentages tallied together:

    Mozilla/4.0 (compatible; MSIE 5.5; Windows NT 5.0)
    Mozilla/4.0 (compatible; MSIE 5.5; Windows NT 5.0; T312461)

    Mozilla/4.0 (compatible; MSIE 6.0; Windows NT 5.0; .NET CLR 1.0.3705)
    Mozilla/4.0 (compatible; MSIE 6.0; Windows NT 5.0; T312461)

    Is that possible?


    "Tom Moreau" <spam.me.cips.ca> wrote in message
    news:phx.gbl...
    Try:

    Select
    BrowserType
    , count (*) * 100.0 / (select count (*) from MyTable) as [Percent]
    from
    MyTable
    group by
    BrowserType

    --
    Tom

    ---------------------------------------------------------------
    Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
    SQL Server MVP
    Columnist, SQL Server Professional
    Toronto, ON Canada
    www.pinnaclepublishing.com/sql


    "Ed" <com> wrote in message
    news:phx.gbl...
    I have a table as follows:

    UserID BrowserType
    1 Mozilla/4.0 (compatible; MSIE 6.0; Windows NT 5.0)
    2 Mozilla/4.0 (compatible; MSIE 6.0; Windows 98)
    3 Mozilla/4.0 (compatible; MSIE 6.0; Windows 98)
    4 Mozilla/4.0 (compatible; MSIE 5.22; Mac_PowerPC)
    5 Mozilla/4.0 (compatible; MSIE 6.0; Windows NT 5.0)
    etc..

    I'd like to get an output as follows:

    50% Mozilla/4.0 (compatible; MSIE 6.0; Windows NT 5.0)
    22% Mozilla/4.0 (compatible; MSIE 6.0; Windows 98)
    28% Mozilla/4.0 (compatible; MSIE 5.22; Mac_PowerPC)

    How do I write a SELECT statement to do that?

    Thank you.


    Ed Guest

  6. #6

    Default Re: About percentages

    Well, now you're getting into display issues. You could certainly put your select into a derived table and then join that to the original table to get the user info. Your front end could then display it as you have shown.

    --
    Tom

    ---------------------------------------------------------------
    Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
    SQL Server MVP
    Columnist, SQL Server Professional
    Toronto, ON Canada
    www.pinnaclepublishing.com/sql


    "Ed" <com> wrote in message news:phx.gbl...
    Thanks. Is it possible to get a report so that under each
    browser+percentage, I can see who the users are?

    Sample output:

    Mozilla/4.0 (compatible; MSIE 6.0; Windows NT 5.0) 56%
    UserID 2
    UserID 23
    UserID 49
    ...
    Mozilla/4.0 (compatible; MSIE 5.5; Windows NT 5.0) 23%
    UserID 6
    UserID 7
    UserID 9
    ...




    "Tom Moreau" <spam.me.cips.ca> wrote in message
    news:%23d$phx.gbl...
    You can use the CASE construct of the form:

    select
    case
    when BrowserType like 'Mozilla/4.0 (compatible; MSIE 5.5; Windows NT
    5.0%'
    then Mozilla/4.0 (compatible; MSIE 5.5; Windows NT 5.0)'
    when BrowserType like 'Mozilla/4.0 (compatible; MSIE 6.0; Windows NT
    5.0%'
    then Mozilla/4.0 (compatible; MSIE 6.0; Windows NT 5.0)'
    end as BrowserType
    , count (*) * 100.0 / (select count (*) from MyTable) as [Percent]
    from
    MyTable
    group by
    case
    when BrowserType like 'Mozilla/4.0 (compatible; MSIE 5.5; Windows NT
    5.0%'
    then Mozilla/4.0 (compatible; MSIE 5.5; Windows NT 5.0)'
    when BrowserType like 'Mozilla/4.0 (compatible; MSIE 6.0; Windows NT
    5.0%'
    then Mozilla/4.0 (compatible; MSIE 6.0; Windows NT 5.0)'
    end

    --
    Tom

    ----------------------------------------------------
    Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
    SQL Server MVP
    Columnist, SQL Server Professional
    Toronto, ON Canada
    www.pinnaclepublishing.com/sql
    ..
    "Ed" <com> wrote in message
    news:%phx.gbl...
    Thanks. That works great.

    I want to know if this is possible. Some of the results that I have in
    BrowserType are varying because a user might have installed a new service
    pack, etc. But overall, they are all referring to the same browser and
    platform. So, I would like to combine, say the following, into the same
    category and get their percentages tallied together:

    Mozilla/4.0 (compatible; MSIE 5.5; Windows NT 5.0)
    Mozilla/4.0 (compatible; MSIE 5.5; Windows NT 5.0; T312461)

    Mozilla/4.0 (compatible; MSIE 6.0; Windows NT 5.0; .NET CLR 1.0.3705)
    Mozilla/4.0 (compatible; MSIE 6.0; Windows NT 5.0; T312461)

    Is that possible?


    "Tom Moreau" <spam.me.cips.ca> wrote in message
    news:phx.gbl...
    Try:

    Select
    BrowserType
    , count (*) * 100.0 / (select count (*) from MyTable) as [Percent]
    from
    MyTable
    group by
    BrowserType

    --
    Tom

    ---------------------------------------------------------------
    Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
    SQL Server MVP
    Columnist, SQL Server Professional
    Toronto, ON Canada
    www.pinnaclepublishing.com/sql


    "Ed" <com> wrote in message
    news:phx.gbl...
    I have a table as follows:

    UserID BrowserType
    1 Mozilla/4.0 (compatible; MSIE 6.0; Windows NT 5.0)
    2 Mozilla/4.0 (compatible; MSIE 6.0; Windows 98)
    3 Mozilla/4.0 (compatible; MSIE 6.0; Windows 98)
    4 Mozilla/4.0 (compatible; MSIE 5.22; Mac_PowerPC)
    5 Mozilla/4.0 (compatible; MSIE 6.0; Windows NT 5.0)
    etc..

    I'd like to get an output as follows:

    50% Mozilla/4.0 (compatible; MSIE 6.0; Windows NT 5.0)
    22% Mozilla/4.0 (compatible; MSIE 6.0; Windows 98)
    28% Mozilla/4.0 (compatible; MSIE 5.22; Mac_PowerPC)

    How do I write a SELECT statement to do that?

    Thank you.



    Tom Guest

Similar Threads

  1. Calculate Ink Percentages in Acrobat Professional
    By tim_zurko@adobeforums.com in forum Adobe Acrobat Macintosh
    Replies: 2
    Last Post: November 6th, 02:35 AM
  2. percentages in mx
    By Moggie webforumsuser@macromedia.com in forum Macromedia Director Basics
    Replies: 2
    Last Post: January 13th, 03:10 PM
  3. onstat -P buffer percentages
    By Glen Johnson in forum Informix
    Replies: 3
    Last Post: November 17th, 01:25 PM
  4. Percentages
    By Gianna in forum Microsoft Access
    Replies: 1
    Last Post: July 1st, 08:12 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139