Professional Web Applications Themes

Rating System / AVG (Easy Question) - Microsoft SQL / MS SQL Server

I am building a rating system, and there will be lots of records that will simply be 1,2,3,4 or 5. tinyInt obviously works fine for this, BUT: I want to do something like 'SELECT AVG(ratings) as avgRating' And this always returns an tinyint datatype since the ratings are tinyints. I want slightly more precision than a tinyint for the average, what I want is something in the format of '#.##' (i.e. 3.45). What datatypes do I need to store the ratings in and what command can I use to get JUST a #.##? I've changed the datatypes to money and ...

  1. #1

    Default Re: Rating System / AVG (Easy Question)

    I am building a rating system, and there will be lots of records that will
    simply be 1,2,3,4 or 5. tinyInt obviously works fine for this, BUT:

    I want to do something like 'SELECT AVG(ratings) as avgRating' And this
    always returns an tinyint datatype since the ratings are tinyints. I want
    slightly more precision than a tinyint for the average, what I want is
    something in the format of '#.##' (i.e. 3.45). What datatypes do I need to
    store the ratings in and what command can I use to get JUST a #.##?

    I've changed the datatypes to money and decimal and used the ROUND
    function,
    but it still returns something like '3.4500'.

    Any help is appreciated, thanks in advance
    Craig



    Craig Guest

  2. #2

    Default Re: Rating System / AVG (Easy Question)

    Hello Craig !

    That´s the way these datatypes are displayed...

    Here is a workaround:

    Select Left(AVG(convert(decimal(4,3),Column)),4) from Table1

    Jens Süßmeyer


     


    Jens Guest

  3. #3

    Default Re: Rating System / AVG (Easy Question)

    Hello Amy !

    I think the problem is that he wants the format #.###.
    I was on the same way as you (only with convert which is the same), but then
    saw his final explanations.

    Jens Süßmeyer.

    "Amy" <com> schrieb im Newsbeitrag
    news:phx.gbl... [/ref]
    will [/ref]
    this 
    > want [/ref]
    need 
    >
    >[/ref]


    Jens Guest

  4. #4

    Default Re: Rating System / AVG (Easy Question)

    >> what I want is something in the format of '#.##' (i.e. 3.45). <<

    In a Client/Server architecture, all data formatting is done in the
    front end, not the database.

    --CELKO--
    ===========================
    Please post DDL, so that people do not have to guess what the keys,
    constraints, Declarative Referential Integrity, datatypes, etc. in your
    schema are.

    *** Sent via Developersdex http://www.developersdex.com ***
    Don't just participate in USENET...get rewarded for it!
    Joe Guest

  5. #5

    Default Re: Rating System / AVG (Easy Question)

    I noticed that after hitting send :-)

    i guess i'm too tired to be replying right now :-) off to sleep we go!

    Thanks and Ciao...

    "Jens Süßmeyer" <jsuessmeyer[REJECT_SPAM]web.de> wrote in message
    news:phx.gbl... 
    then [/ref]
    > will [/ref]
    > this 
    > > want [/ref][/ref]
    is [/ref]
    > need 
    > >
    > >[/ref]
    >
    >[/ref]


    Amy Guest

  6. #6

    Default Re: Rating System / AVG (Easy Question)

    I know that is the generally accepted practice, but performance isn't a very
    big concern. I'm using Data Binding in .Net and it's SO easy if you just do
    a small amount of formatting inside the query just to make a very clean data
    bind in .Net. (Only applies if you're not manipulating the data, even for
    display)

    It is likely that for this application the web server is going to be the
    same box as the dB server, so it doesn't really matter where the calculation
    is done, the performance impact is on the same box and for this project it
    would be splitting hairs to determine which did it more efficiently.



    "Joe Celko" <edu> wrote in message
    news:phx.gbl... [/ref]
    >
    > In a Client/Server architecture, all data formatting is done in the
    > front end, not the database.
    >
    > --CELKO--
    > ===========================
    > Please post DDL, so that people do not have to guess what the keys,
    > constraints, Declarative Referential Integrity, datatypes, etc. in your
    > schema are.
    >
    > *** Sent via Developersdex http://www.developersdex.com ***
    > Don't just participate in USENET...get rewarded for it![/ref]


    Craig Guest

Similar Threads

  1. Rating System
    By ism in forum Macromedia ColdFusion
    Replies: 9
    Last Post: May 9th, 01:41 PM
  2. Easy question = easy answer?
    By Jyry webforumsuser@macromedia.com in forum Macromedia Freehand
    Replies: 4
    Last Post: September 29th, 03:29 PM
  3. Is there an easy way to restore the system?
    By Wathen, in forum Debian
    Replies: 4
    Last Post: August 13th, 07:00 PM
  4. Easy Question/Easy Answer
    By ChuckyJ webforumsuser@macromedia.com in forum Macromedia Dreamweaver
    Replies: 1
    Last Post: July 12th, 01:50 AM

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