Results 1 to 13 of 13
  1. #1
    Join Date
    Aug 2007
    Posts
    32

    Unanswered: group function not allowed...how to fix?

    Hi. I am creating a database through oracle as part of a DVD hire system. Basically, one of the queries i have to build has to list those customers who have a total number of rentals within 10 percent of the customer with the most rentals. I have made this so far, but I cant get it working properly.....

    Code:
    /* Query for finding customers to reward */
    SELECT MEMBER.MEMBER_NO AS "Member No",
           MEMBER.MEMBER_GNAME || ' ' || MEMBER.MEMBER_FNAME AS "Member Name",
           MEMBER.MEMBER_PHONE AS "Member Phone",
           COUNT(*) AS "Count"
    FROM   MEMBER,RENTAL
    WHERE  RENTAL.MEMBER_NO=MEMBER.MEMBER_NO AND "Count" >= MAX(("Count")*0.9)
    
    GROUP BY MEMBER.MEMBER_NO, MEMBER.MEMBER_GNAME, MEMBER.MEMBER_FNAME,MEMBER.MEMBER_PHONE
    ORDER BY "Count" DESC;
    I get the error "group function not allowed here"

    Any way to fix this that anybody knows?

    Many thanks

  2. #2
    Join Date
    Sep 2007
    Posts
    12
    Code:
    SELECT MEMBER.MEMBER_NO AS "Member_No",
           MEMBER.MEMBER_GNAME || ' ' || MEMBER.MEMBER_FNAME AS "Member_Name",
           MEMBER.MEMBER_PHONE AS "Member_Phone",
           COUNT(*) AS "Count"
    FROM   MEMBER, RENTAL
    WHERE  RENTAL.MEMBER_NO=MEMBER.MEMBER_NO AND "Count" >= (select MAX(count(*))*0.9 from MEMBER, RENTAL GROUP BY MEMBER.MEMBER_NO, MEMBER.MEMBER_GNAME, MEMBER.MEMBER_FNAME,MEMBER.MEMBER_PHONE)
    
    GROUP BY MEMBER.MEMBER_NO, MEMBER.MEMBER_GNAME, MEMBER.MEMBER_FNAME,MEMBER.MEMBER_PHONE
    ORDER BY "Count" DESC
    You can't use group function (MAX, COUNT in this case) within WHERE clause.

  3. #3
    Join Date
    Aug 2007
    Posts
    32
    Quote Originally Posted by lubogabal
    Code:
    SELECT MEMBER.MEMBER_NO AS "Member_No",
           MEMBER.MEMBER_GNAME || ' ' || MEMBER.MEMBER_FNAME AS "Member_Name",
           MEMBER.MEMBER_PHONE AS "Member_Phone",
           COUNT(*) AS "Count"
    FROM   MEMBER, RENTAL
    WHERE  RENTAL.MEMBER_NO=MEMBER.MEMBER_NO AND "Count" >= (select MAX(count(*))*0.9 from MEMBER, RENTAL GROUP BY MEMBER.MEMBER_NO, MEMBER.MEMBER_GNAME, MEMBER.MEMBER_FNAME,MEMBER.MEMBER_PHONE)
    
    GROUP BY MEMBER.MEMBER_NO, MEMBER.MEMBER_GNAME, MEMBER.MEMBER_FNAME,MEMBER.MEMBER_PHONE
    ORDER BY "Count" DESC
    You can't use group function (MAX, COUNT in this case) within WHERE clause.
    OO thats what I was thinking.. do you have any ideas how i can get around this problem?

  4. #4
    Join Date
    Sep 2007
    Posts
    12
    Quote Originally Posted by lockmac
    OO thats what I was thinking.. do you have any ideas how i can get around this problem?
    I already posted solution

  5. #5
    Join Date
    Aug 2007
    Posts
    32
    Thanks heaps mate..but i am getting the following output..

    Code:
    Error starting at line 1 in command:
    SELECT MEMBER.MEMBER_NO AS "Member_No",
           MEMBER.MEMBER_GNAME || ' ' || MEMBER.MEMBER_FNAME AS "Member_Name",
           MEMBER.MEMBER_PHONE AS "Member_Phone",
           COUNT(*) AS "Count"
    FROM   MEMBER, RENTAL
    WHERE  RENTAL.MEMBER_NO=MEMBER.MEMBER_NO AND "Count" >= (SELECT MAX(count(*))*0.9 from MEMBER, RENTAL GROUP BY MEMBER.MEMBER_NO, MEMBER.MEMBER_GNAME, MEMBER.MEMBER_FNAME,MEMBER.MEMBER_PHONE)
    
    GROUP BY MEMBER.MEMBER_NO, MEMBER.MEMBER_GNAME, MEMBER.MEMBER_FNAME,MEMBER.MEMBER_PHONE
    ORDER BY "Count" DESC
    Error at Command Line:6 Column:45
    Error report:
    SQL Error: ORA-00904: "Count": invalid identifier
    00904. 00000 -  "%s: invalid identifier"
    *Cause:    
    *Action:
    This seems to be the thing that I cant get my head around. Basically I can sorta write the code for most things, but I always get this error, and am not sre what Im doing wrong. Any ideas?

  6. #6
    Join Date
    Sep 2007
    Posts
    12
    [QUOTE=lockmac]Thanks heaps mate..but i am getting the following output..
    Code:
    SELECT Member_No, Member_Name, Member_Phone FROM (
    SELECT MEMBER.MEMBER_NO AS "Member_No",
           MEMBER.MEMBER_GNAME || ' ' || MEMBER.MEMBER_FNAME AS "Member_Name",
           MEMBER.MEMBER_PHONE AS "Member_Phone",
           COUNT(*) AS "Count"
    FROM   MEMBER, RENTAL
    WHERE  RENTAL.MEMBER_NO=MEMBER.MEMBER_NO
    
    GROUP BY MEMBER.MEMBER_NO, MEMBER.MEMBER_GNAME, MEMBER.MEMBER_FNAME,MEMBER.MEMBER_PHONE)
    WHERE Count >= (SELECT MAX(count(*))*0.9 from MEMBER, RENTAL GROUP BY MEMBER.MEMBER_NO, MEMBER.MEMBER_GNAME, MEMBER.MEMBER_FNAME,MEMBER.MEMBER_PHONE)
    ORDER BY "Count" DESC
    try this one ...

  7. #7
    Join Date
    Aug 2007
    Posts
    32
    Code:
    Error starting at line 1 in command:
    SELECT Member_No, Member_Name, Member_Phone FROM (
    SELECT MEMBER.MEMBER_NO AS "Member_No",
           MEMBER.MEMBER_GNAME || ' ' || MEMBER.MEMBER_FNAME AS "Member_Name",
           MEMBER.MEMBER_PHONE AS "Member_Phone",
           COUNT(*) AS "Count"
    FROM   MEMBER, RENTAL
    WHERE  RENTAL.MEMBER_NO=MEMBER.MEMBER_NO
    
    GROUP BY MEMBER.MEMBER_NO, MEMBER.MEMBER_GNAME, MEMBER.MEMBER_FNAME,MEMBER.MEMBER_PHONE)
    WHERE Count >= (SELECT MAX(count(*))*0.9 from MEMBER, RENTAL GROUP BY MEMBER.MEMBER_NO, MEMBER.MEMBER_GNAME, MEMBER.MEMBER_FNAME,MEMBER.MEMBER_PHONE)
    ORDER BY "Count" DESC
    Error at Command Line:10 Column:6
    Error report:
    SQL Error: ORA-00904: "COUNT": invalid identifier
    00904. 00000 -  "%s: invalid identifier"
    *Cause:    
    *Action:
    It definately doesnt like the "Count" word being their. With the first code you wrote, it worked if I substitued "Count" in the Where clause for something else, such as an attribute of MOVIE, but obviously thats not what I need my query to do ey..........

  8. #8
    Join Date
    Sep 2007
    Posts
    12
    Quote Originally Posted by lockmac
    It definately doesnt like the "Count" word being their. With the first code you wrote, it worked if I substitued "Count" in the Where clause for something else, such as an attribute of MOVIE, but obviously thats not what I need my query to do ey..........
    just replace first line of the code by this one, I forgot about it:

    SELECT Member_No, Member_Name, Member_Phone, Count FROM (

  9. #9
    Join Date
    Aug 2007
    Posts
    32
    Code:
    Error starting at line 1 in command:
    SELECT Member_No, Member_Name, Member_Phone, Count FROM (
    SELECT MEMBER.MEMBER_NO AS "Member_No",
           MEMBER.MEMBER_GNAME || ' ' || MEMBER.MEMBER_FNAME AS "Member_Name",
           MEMBER.MEMBER_PHONE AS "Member_Phone",
           COUNT(*) AS "Count"
    FROM   MEMBER, RENTAL
    WHERE  RENTAL.MEMBER_NO=MEMBER.MEMBER_NO
    
    GROUP BY MEMBER.MEMBER_NO, MEMBER.MEMBER_GNAME, MEMBER.MEMBER_FNAME,MEMBER.MEMBER_PHONE)
    WHERE Count >= (SELECT MAX(count(*))*0.9 from MEMBER, RENTAL GROUP BY MEMBER.MEMBER_NO, MEMBER.MEMBER_GNAME, MEMBER.MEMBER_FNAME,MEMBER.MEMBER_PHONE)
    ORDER BY "Count" DESC
    Error at Command Line:10 Column:6
    Error report:
    SQL Error: ORA-00904: "COUNT": invalid identifier
    00904. 00000 -  "%s: invalid identifier"
    *Cause:    
    *Action:
    Hmmmmmmm.... same message. BTW thanks for your help up until here

  10. #10
    Join Date
    Jun 2004
    Location
    Liverpool, NY USA
    Posts
    2,509
    Please do NOT use count as a column name. It is a reserved oracle name and is not allowed.
    Bill
    You do not need a parachute to skydive. You only need a parachute to skydive twice.

  11. #11
    Join Date
    Mar 2007
    Posts
    623
    As WHERE clause is evaluated before GROUP BY clause (check eg. here), you cannot use aggregated values there.
    Put this condition into HAVING clause instead.

  12. #12
    Join Date
    Aug 2007
    Posts
    32
    thanks guys ive got it all worked out now. thanks for all your help

    Code:
    SELECT MEMBER.MEMBER_NO AS "Member No",
           MEMBER.MEMBER_GNAME || ' ' || MEMBER.MEMBER_FNAME AS "Member Name",
           MEMBER.MEMBER_PHONE AS "Member Phone",
           COUNT(*) AS "ABC"
    FROM   MEMBER, RENTAL
    WHERE  RENTAL.MEMBER_NO=MEMBER.MEMBER_NO
    GROUP BY MEMBER.MEMBER_NO, MEMBER.MEMBER_GNAME, MEMBER.MEMBER_FNAME,MEMBER.MEMBER_PHONE
    HAVING COUNT(*) >= ((SELECT max(COUNT(*)) FROM MEMBER,RENTAL WHERE RENTAL.MEMBER_NO=MEMBER.MEMBER_NO GROUP BY MEMBER.MEMBER_FNAME)*0.9)
    ORDER BY "ABC" DESC;

  13. #13
    Join Date
    Nov 2002
    Posts
    272
    That looks like it would work.
    I would give the Count column a more meaningful name than "ABC" though.

Posting Permissions

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