Oracle - Error returning "Name" + Count() - "not a GROUP BY Expression"

Asked

Viewed 493 times

0

Hello! I’m trying to return the names of people and the amount of cars each has according to the 2 tables below, but accuses the following error:

ORA-00979: not a GROUP BY Expression 00979. 00000 - "not a GROUP BY Expression"

Here are my 2 tables:

create table pessoa(
idpessoa number not null,
nome varchar2(40) not null,
constraint pk_idpessoa primary key(idpessoa)
);

create table carro(
idcarro number not null,
nome_carro varchar2(40) not null,
idpessoa number not null,
constraint fk_idcarro foreign key(idpessoa) references pessoaA(idpessoa)
);

/*INSERT'S CASO VOCÊ QUEIRA TESTAR*/
insert into pessoa values(1, 'Maria');
insert into pessoa values(2, 'Joao');

insert into carro values(1, 'Fusca', 1);
insert into carro values(2, 'Kombi', 1);
insert into carro values(3, 'Opala', 2);

Return only quantity works...

select count(carro.idcarro) from carro group by carro.idpessoa;

However, when I put to return also the person’s name results in error

select pessoa.nome, count(carro.idcarro) from pessoa, carro group by carro.idpessoa;

I tested this last line this way because in Mysql it works, already in Oracle no :/ . Can someone help me?

oracle
  • 1

    select person.name, Count(car.idcar) from person, car group by person.name; .... in Oracle the select fields must be in "group by"

  • Thank you very much! I didn’t know it! Although the code is still not generating what I expect (it doesn’t show each person but the total), it helped me a lot!

1 answer

1

Fortunately with Motta’s comment I was able to solve my problem by putting the SELECT field in GROUP BY and adding a WHERE:

select pessoa.nome, count(carro.idcarro) from pessoa, carro
      where pessoa.idpessoa=carro.idpessoa
      group by carro.idpessoa, pessoa.nome
      order by carro.idpessoa;

Browser other questions tagged oracle

You are not signed in. Login or sign up in order to post.