Results 1 to 9 of 9
  1. #1
    Join Date
    Oct 2013
    Posts
    36

    Unanswered: ORA-00937: (not a single-group group function) error?

    I get the error with my following code, it is supposed to find the monthly average revenue from my customers,
    and rounding to the nearest penny. Only two tables are involved (m_customers & m_membership_type.)

    select round(avg(monthly_fee), 2), customer_id

    from m_membership_type, m_customer

    where m_membership_type.membership_type_code = m_customer.membership_type_code
    Last edited by london34; 11-26-13 at 14:21.
    Still learning..

  2. #2
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    Code:
    SELECT Round(Avg(monthly_fee), 2), 
           customer_id 
    FROM   m_membership_type, 
           m_customer 
    WHERE  m_membership_type.membership_type_code = m_customer.membership_type_code 
    GROUP  BY customer_id
    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
    Oct 2013
    Posts
    36
    That was too obvious :/

    Thanks.
    Last edited by london34; 11-26-13 at 15:08.
    Still learning..

  4. #4
    Join Date
    Oct 2013
    Posts
    36
    Didn't want to create a new thread, as it's another error related issue. I am now getting the ORA-01858: a non-numeric character was found where a numeric was expected error for the following code. The code works up until the last line, where I am supposed to output customers that were older than 60 years old when they downloaded a movie, thus the 720 months sum.


    SELECT m_downloads.download_date,

    m_movies.movie_id,

    m_movies.title_of_movie,

    m_customer.d_o_b

    FROM m_membership_type,

    m_customer,

    m_downloads,

    m_classification_code,

    m_movies

    WHERE m_downloads.customer_id=m_customer.customer_id

    AND m_membership_type.membership_type_code=m_customer. membership_type_code

    AND download_date >= add_months(d_o_b, 720)

    EDIT: I think I know the problem, during table creation I set my download dates as date where as date_of_birth I have stupidly set as VARCHAR2, will fix now and report back.
    Last edited by london34; 11-26-13 at 15:13.
    Still learning..

  5. #5
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    Code:
    SELECT m_downloads.download_date, 
           m_movies.movie_id, 
           m_movies.title_of_movie, 
           m_customer.d_o_b 
    FROM   m_membership_type, 
           m_customer, 
           m_downloads, 
           m_classification_code, 
           m_movies 
    WHERE  m_downloads.customer_id = m_customer.customer_id 
           AND m_membership_type.membership_type_code = 
               m_customer. membership_type_code 
           AND download_date >= Add_months(d_o_b, 720)
    when you have five tables in FROM clause you need a minimum of four compares in WHERE clause; otherwise Cartesian Product results
    which I expect will occur for this SQL

    are both DOWNLOAD_DATE & D_O_B DATE datatypes?
    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.

  6. #6
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    IMO, with well constructed SELECT statement only those tables which project columns into the SELECT clause should exist within the FROM clause.
    if/when additional tables are required for desired filtering, then additional tables should be subordinated into the WHERE clause.
    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.

  7. #7
    Join Date
    Oct 2013
    Posts
    36
    Yes you're right. I changed the d_o_b to date, re-added all data and tried the query, got a cartesian product. Turns out I forgot to match my movie.id's (highlighted green below) so the new code works fine now.


    select m_downloads.download_date,

    m_movies.movie_id,

    m_movies.title_of_movie,

    m_customer.d_o_b

    from m_membership_type,

    m_customer,

    m_downloads,

    m_classification_code,

    m_movies

    where m_downloads.customer_id=m_customer.customer_id

    and m_downloads.movie_id=m_movies.movie_id

    and m_membership_type.membership_type_code=m_customer. membership_type_code

    and download_date >= add_months(d_o_b, 720)


    Read your second response, Ok, I will keep that in mind.
    Still learning..

  8. #8
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    http://www.dbforums.com/oracle/10316...s-posters.html
    read URL above to learn how to use {code} tags
    Instant SQL Formatter
    use URL above to properly format SQL for improved readability

    compare your SQL code postings to mine
    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.

  9. #9
    Join Date
    Oct 2013
    Posts
    36
    Quote Originally Posted by anacedent View Post
    http://www.dbforums.com/oracle/10316...s-posters.html
    read URL above to learn how to use {code} tags
    Instant SQL Formatter
    use URL above to properly format SQL for improved readability

    compare your SQL code postings to mine
    Ok, thanks for those. The Instant formatter will greatly help tidying up my portfolio. I will post SQL accordingly from now on on this site.
    Still learning..

Posting Permissions

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