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 ElisaHello, the problem is that you use a aggregate command "count" in the select statement and Oracle does not know how to hande 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|||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:

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:

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.|||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:

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:

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)|||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.|||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