Results 1 to 6 of 6
  1. #1
    Join Date
    Oct 2005
    Posts
    92

    Unanswered: Find Average rates

    Hi All,

    Input:

    Customer Rating
    100 3
    100 4
    101 5
    101 2
    100 5
    100 3
    101 4
    103 4


    Output:

    Customer Rating
    100 4
    101 4
    103 4

    Can somoene please help me how can I calculate average per customer.

    Also if the average for customer 100 is 3.75 it has to be rounded to 4.

    Thanks
    Pavan

  2. #2
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    SQL> select round(3.75) from dual;

    ROUND(3.75)
    -----------
    4

    SQL>
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  3. #3
    Join Date
    Nov 2003
    Posts
    2,933
    Provided Answers: 12
    Quote Originally Posted by pavan_test View Post
    Can somoene please help me how can I calculate average per customer.
    That is very basic SQL that is covered in every SQL tutorial. You might want to go through this one for a start: http://sqlzoo.net/wiki/SUM_and_COUNT
    I will not read nor answer questions where the SQL code is messy and not formatted properly using [code] tags: http://www.dbforums.com/misc.php?do=bbcode#code

    Tips for good questions:

    http://tkyte.blogspot.de/2005/06/how...questions.html
    http://wiki.postgresql.org/wiki/SlowQueryQuestions
    http://catb.org/esr/faqs/smart-questions.html

  4. #4
    Join Date
    Dec 2012
    Location
    Logan, Utah
    Posts
    163
    Yeah, on the surface avg is basic stuff. But when you throw in round, ceiling, floor, and cast it gets a bit more involved and requires a good understanding of what you want and the data involved.

    Code:
    declare @t table 
    (
    	id int,
    	Rate int
    )
    
     insert into @t
     select 100, 3 union 
     select 100, 4 union 
     select 101, 5 union  
     select 101, 2 union 
     select 100, 5 union 
     select 100, 3 union
     select 101, 4 union 
     select 103, 4
     
     select id, SUM(rate) summed COUNT(*) cnt, 
            cast(SUM(rate) as float) / cast(COUNT(*) as float) RealAvg, 
            CEIL(AVG(cast(Rate as float))) ForcedAvg from @t
     group by id

  5. #5
    Join Date
    Oct 2005
    Posts
    92

    Find Average rates

    Thank you LinksUp for your help. Your suggestion worked for me

    SELECT id,
    SUM (RATE),
    COUNT ( * ) cnt,
    CAST (SUM (RATE) AS float)
    / CAST (COUNT ( * ) AS float)
    realavg,
    CEIL (AVG (CAST (RATE AS float))) forcedavg
    FROM table1
    group by id;

    Thanks
    Pavan

  6. #6
    Join Date
    Jun 2004
    Location
    Liverpool, NY USA
    Posts
    2,509
    just an idea, oracle has a build in function. try.

    SELECT id,
    SUM (RATE),
    COUNT ( * ) cnt,
    avg(rate) realavg,
    round(AVG (RATE)) forcedavg
    FROM table1
    group by id;
    Bill
    You do not need a parachute to skydive. You only need a parachute to skydive twice.

Posting Permissions

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