Oracle - Error returning "Name" + count () - "not a GROUP BY expression"

Question

Oracle - Error returning "Name" + count () - "not a GROUP BY expression"

Navigation

#1 by (0 votes)

0

Hello! I am trying to return people's names 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 the quantity works ...

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

However, when I put in to return the person's name also 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, since in Oracle it does not: /. Can anyone help me?

oracle

asked by anonymous 21.10.2016 / 01:48

1 answer

Release access to www with Apache2 Register external employees Validation mysql php

score 0 · 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;