Query with Count and GroupBy

Posted 9 months ago by Carlos_rsd

Good afternoon. I have the following tables: one for students, one for the regions where students live, they are related through the IdRegioes field.

I would like to bring the data on student counts by region, according to some criteria to be displayed in my summary view, using SQL in the database I would have the following query:

SELECT i.regioes_idregioes, r.regiao, COUNT(i.regioes_idregioes) AS quantidade FROM inscricoes as i, regioes as r WHERE i.Regioes_IdRegioes = r.IdRegiao and i.Atendido ='N' and i.Evadido = 'N' and i.Desistente = 'N' and i.DataNascimento >= '2015-03-31' GROUP BY i.regioes_idregioes;

When transporting this query to an Eloquent template I did the following:

$TotInscRegioes = Inscricao::selectRaw('inscricoes.Regioes_IdRegioes',['count(*) as TotRegiao'],'Regioes.Regiao') ->join('Regioes','inscricoes.Regioes_IdRegioes','=','Regioes.IdRegiao') ->where('Atendido','N')->where('Desistente','N')->where('Evadido','N') ->where('DataNascimento','>=',$dataLimite) ->groupBy('inscricoes.Regioes_IdRegioes','Regioes.Regiao') ->get();

However, I always get the general total of students and not the counting by region. I already researched several and I can not find a way out, have you ever had a situation like this? What was the solution you found?

Note: I have already tried using select and putting -> count () at the end and even then the data did not come apart.

Thank you for help.

Please sign in or create an account to participate in this conversation.