none
Problem with an exercise from school ^^ RRS feed

  • Question

  • I need to give the average books by category, and the result need to be rounded to 1 number.
    there are 20 books and 3 category's so result is 6.66666666 but it need to be rounded to 7 and thats where i'm stuck ^^ my result is after rounding 6


    this is what i have:

    select count(title)/ cast (count(distinct category) as decimal) as average_books
    from books b, category c
    where c.cat_id = b.cat_id

    or

    select count(title)*1.00/ count(distinct category)*1.00 as average_books
    from books b, category c
    where c.cat_id = b.cat_id


    sorry for my english i'm dutch :)
    greetz
    Tuesday, December 1, 2009 11:30 AM

Answers

  • Hi,
    Please try this round function

    select round(count(title)/ cast (count(distinct category) as decimal) ,0) as average_books
    from books b, category c
    where c.cat_id = b.cat_id

    Thanks,
    Gnanasekar Babu

    Please vote "yes" if does help you
    Thanks, Gnanasekar Babu Note: Please click on the vote button if the answer helps you
    • Proposed as answer by JGSB Tuesday, December 1, 2009 11:43 AM
    • Marked as answer by Miguel VdB Tuesday, December 1, 2009 11:50 AM
    Tuesday, December 1, 2009 11:35 AM

All replies

  • Hi,
    Please try this round function

    select round(count(title)/ cast (count(distinct category) as decimal) ,0) as average_books
    from books b, category c
    where c.cat_id = b.cat_id

    Thanks,
    Gnanasekar Babu

    Please vote "yes" if does help you
    Thanks, Gnanasekar Babu Note: Please click on the vote button if the answer helps you
    • Proposed as answer by JGSB Tuesday, December 1, 2009 11:43 AM
    • Marked as answer by Miguel VdB Tuesday, December 1, 2009 11:50 AM
    Tuesday, December 1, 2009 11:35 AM
  • Thanks this worked perfect...

    Just... its was 7.000000
    But i  solved this problem by chance decimal to float and now its 7 :)

    Thanks alot

    greetz
    Tuesday, December 1, 2009 11:52 AM