Results 1 to 6 of 6
  1. #1
    Join Date
    Aug 2003
    Location
    Vicenza(I)
    Posts
    21

    Unanswered: error ora -00937

    hi,
    what's the problem?
    When I execute this query

    select decode((magazzino.QUANTITÀ - (count(noleggio.data_noleggio) + count(prenotazione.data_prenotazione))),0,'false', 1,'true','true') as disponibile
    from MAGAZZINO,FILM,PRENOTAZIONE,NOLEGGIO,NEGOZIO
    where magazzino.codfilm=film.idfilm and magazzino.CODNEGOZIO=negozio.idnegozio
    and prenotazione.codfilm=film.idfilm and prenotazione.CODNEGOZIO=negozio.idnegozio
    and noleggio.codfilm=film.idfilm and noleggio.CODNEGOZIO=negozio.idnegozio
    and film.titolo='Matrix' and negozio.idnegozio=1

    the result is:
    Error: ORA-00937: not a single-group group function.

    Thank you Elisa

  2. #2
    Join Date
    Jul 2001
    Location
    Germany
    Posts
    189

    Re: error ora -00937

    Hello, the problem is that you use a aggregate command "count" in the select statement and Oracle does not know how to hanöde the field
    magazzino.QUANTITÀ ...

    magazzino.QUANTITÀ -> every record
    count -> aggregate of result set

    use the group by function or an inner select to calculate the count of your fields.

    Hope that helps ?

    Manfred Peter
    Alligator Company Software GmbH
    http://www.alligatorsql.com



    Originally posted by trilly
    hi,
    what's the problem?
    When I execute this query

    select decode((magazzino.QUANTITÀ - (count(noleggio.data_noleggio) + count(prenotazione.data_prenotazione))),0,'false', 1,'true','true') as disponibile
    from MAGAZZINO,FILM,PRENOTAZIONE,NOLEGGIO,NEGOZIO
    where magazzino.codfilm=film.idfilm and magazzino.CODNEGOZIO=negozio.idnegozio
    and prenotazione.codfilm=film.idfilm and prenotazione.CODNEGOZIO=negozio.idnegozio
    and noleggio.codfilm=film.idfilm and noleggio.CODNEGOZIO=negozio.idnegozio
    and film.titolo='Matrix' and negozio.idnegozio=1

    the result is:
    Error: ORA-00937: not a single-group group function.

    Thank you Elisa

  3. #3
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1

    Re: error ora -00937

    Originally posted by trilly
    hi,
    what's the problem?
    When I execute this query

    select decode((magazzino.QUANTITÀ - (count(noleggio.data_noleggio) + count(prenotazione.data_prenotazione))),0,'false', 1,'true','true') as disponibile
    from MAGAZZINO,FILM,PRENOTAZIONE,NOLEGGIO,NEGOZIO
    where magazzino.codfilm=film.idfilm and magazzino.CODNEGOZIO=negozio.idnegozio
    and prenotazione.codfilm=film.idfilm and prenotazione.CODNEGOZIO=negozio.idnegozio
    and noleggio.codfilm=film.idfilm and noleggio.CODNEGOZIO=negozio.idnegozio
    and film.titolo='Matrix' and negozio.idnegozio=1

    the result is:
    Error: ORA-00937: not a single-group group function.

    Thank you Elisa
    When you use aggregate functions like COUNT together with other columns, you have to add a GROUP BY clause to the query. For example, this doesn't work:

    Code:
    SQL> select deptno, count(*)
      2  from emp
      3  /
    select deptno, count(*)
           *
    ERROR at line 1:
    ORA-00937: not a single-group group function
    But this does:

    Code:
    SQL> select deptno, count(*)
      2  from emp
      3  group by deptno;
    
        DEPTNO   COUNT(*)
    ---------- ----------
            10          3
            20          5
            30          6
    I don't understand what your query is trying to do. Perhaps if you explain the requirement, we can advise on the proper syntax.

  4. #4
    Join Date
    Aug 2003
    Location
    Vicenza(I)
    Posts
    21

    Re: error ora -00937

    Originally posted by andrewst
    When you use aggregate functions like COUNT together with other columns, you have to add a GROUP BY clause to the query. For example, this doesn't work:

    Code:
    SQL> select deptno, count(*)
      2  from emp
      3  /
    select deptno, count(*)
           *
    ERROR at line 1:
    ORA-00937: not a single-group group function
    But this does:

    Code:
    SQL> select deptno, count(*)
      2  from emp
      3  group by deptno;
    
        DEPTNO   COUNT(*)
    ---------- ----------
            10          3
            20          5
            30          6
    I don't understand what your query is trying to do. Perhaps if you explain the requirement, we can advise on the proper syntax.
    I want to know how many dvd or vhs for a specific film I have into a particular negozio.
    The total number of films that I have into my negozio is contained on the table magazzino ,I have to count the total number of films that are out of my negozio ,counting reservations and rents which are in the table "prenotazione" e "noleggio" for that film in that negozio for that types of support.
    do you understand?thank you elisa and my english teacher elena
    Table:
    CREATE TABLE Magazzino
    (CodFilm NUMBER(7) NOT NULL,
    CodNegozio NUMBER(8) NOT NULL,
    Tipo VARCHAR2(3) NOT NULL,
    Quantità NUMBER(4) NOT NULL,
    Evetuali_Commenti VARCHAR2(30),
    PRIMARY KEY(CodFilm,CodNegozio,Tipo)
    FOREIGN KEY(CodNegozio)
    REFERENCES Negozio(IdNegozio)
    ON DELETE CASCADE,
    FOREIGN KEY(CodFilm)
    REFERENCES Film(IdFilm)
    ON DELETE CASCADE)

    CREATE TABLE Prenotazione
    (CodFilm NUMBER(7) NOT NULL,
    CodNegozio NUMBER(8) NOT NULL,
    CodCliente VARCHAR(10) NOT NULL,
    Data_Prenotazione NUMBER(8) NOT NULL,
    Ora_Prenotazione NUMBER(5) NOT NULL,
    Data_Scadenza_Prenotazione NUMBER(8) NOT NULL,
    Ora_Scadenza_Prenotazione NUMBER(5) NOT NULL,
    Supporto VARCHAR2(3) NOT NULL,
    PRIMARY KEY(CodFilm,CodNegozio,CodCliente,Data_Prenotazion e,Ora_Prenotazione,Supporto)
    FOREIGN KEY(CodNegozio)
    REFERENCES Negozio(IdNegozio)
    ON DELETE CASCADE,
    FOREIGN KEY(CodFilm)
    REFERENCES Film(IdFilm)
    ON DELETE CASCADE,
    FOREIGN KEY(CodCliente)
    REFERENCES Cliente(User_id)
    ON DELETE CASCADE)

    CREATE TABLE Noleggio
    (CodFilm NUMBER(7) NOT NULL,
    CodNegozio NUMBER(8) NOT NULL,
    CodCliente VARCHAR(10) NOT NULL,
    Data_Noleggio NUMBER(8) NOT NULL,
    Ora_Noleggio NUMBER(5) NOT NULL,
    Supporto VARCHAR2(3) NOT NULL,
    PRIMARY KEY (CodFilm,CodNegozio,CodCliente,Data_noleggio,Ora_N oleggio,Supporto)
    FOREIGN KEY(CodNegozio)
    REFERENCES Negozio(IdNegozio)
    ON DELETE CASCADE,
    FOREIGN KEY(CodFilm)
    REFERENCES Film(IdFilm)
    ON DELETE CASCADE,
    FOREIGN KEY(CodCliente)
    REFERNCES Cliente(User_id)
    ON DELETE CASCADE)

  5. #5
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1

    Re: error ora -00937

    I can't write the query, but it sounds like you want to do this:
    1) count number of records in Magazzino for that film = A
    2) count number of records in Prenotazione for that film = B
    3) count number of records in Noleggio for that film = C
    4) Return answer = A - B - C

    This can be done using "scalar subqueries", like this:

    SELECT (select count(*) from Magazzino where ... )
    - (select count(*) from Prenotazione where ... )
    - (select count(*) from Noleggio where ... )
    FROM DUAL;

    If required, each scalar subquery can be joined to the outer query, e.g.

    SELECT (select count(*) from Magazzino M where M.codfilm = F.idfilm AND... )
    - (select count(*) from Prenotazione P where P.codfilm = F.idfilm AND... )
    - (select count(*) from Noleggio N where N.codfilm = F.idfilm AND... )
    FROM film F, ...
    WHERE F.titolo='Matrix'
    AND ...;

    Hope that gets you started.

  6. #6
    Join Date
    Aug 2003
    Location
    Vicenza(I)
    Posts
    21

    Re: error ora -00937

    Originally posted by andrewst
    I can't write the query, but it sounds like you want to do this:
    1) count number of records in Magazzino for that film = A
    2) count number of records in Prenotazione for that film = B
    3) count number of records in Noleggio for that film = C
    4) Return answer = A - B - C

    This can be done using "scalar subqueries", like this:

    SELECT (select count(*) from Magazzino where ... )
    - (select count(*) from Prenotazione where ... )
    - (select count(*) from Noleggio where ... )
    FROM DUAL;

    If required, each scalar subquery can be joined to the outer query, e.g.

    SELECT (select count(*) from Magazzino M where M.codfilm = F.idfilm AND... )
    - (select count(*) from Prenotazione P where P.codfilm = F.idfilm AND... )
    - (select count(*) from Noleggio N where N.codfilm = F.idfilm AND... )
    FROM film F, ...
    WHERE F.titolo='Matrix'
    AND ...;

    Hope that gets you started.
    Thank you very much for your avaiable, Elisa

Posting Permissions

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