Count number of people from a table by age group, gender and company

Question

I’m having a hard time getting one working select where to bring the amount of people who work in a enterprise, banded age, age and sex.

I did the example below, but so far I could not make it work, accuses error in alias.

qr_consulta.Close;    
qr_consulta.SQL.Clear;    
qr_consulta.SQL.Add('Select COUNT(*) as total FROM tb_pessoas where ps_idade  >= 34 and ps_idade <= 38 AND em_id =:r AND ps_sexo = "Masculino"' );      
qr_consulta.Params.ParamByName('r').AsInteger := result ;      
qr_consulta.Open();

Edited Guys the above query is working normally, but the way this one is calculating the direct age (I was saving the age not the date of birth), I changed the field to date in the database and now I’m saving the date of birth, I need you to bring in the number of people I’ve broken up by the company, age based on date of birth, compare the age range I defined and the gender. I tried the query below but it’s not working.

result := dbl_consulta.KeyValue;
  qr_consulta.Close;
  qr_consulta.SQL.Clear;
  qr_consulta.SQL.Add('Select COUNT(*) as total FROM tb_pessoas where (SELECT TIMESTAMPDIFF(YEAR, p.ps_idade, CURDATE()) as idade FROM tb_pessoas p) idade >= 29 and idade <= 33 AND em_id =:r AND ps_sexo = "Masculino"' );

The tables are like this:

tb_pessoas

ps_id
ps_nome
ps_idade
ps_sexo
em_id

tb_empresas
em_id
em_nome
em_endereco
em_cnpj
em_telefone

Answer 1

To do this you have to add up the amount of people grouped by company and by gender. Use this query:

SELECT COUNT(tb_pessoas.ps_id) AS quantidade, tb_empresas.em_nome, tb_pessoas.ps_sexo
FROM tb_pessoas
INNER JOIN tb_empresas ON tb_empresas.em_id = tb_pessoas.em_id
WHERE TIMESTAMPDIFF(YEAR, ps_idade, NOW()) BETWEEN 29 AND 33 
AND tb_pessoas.ps_sexo = "Masculino"
AND tb_empresas.em_nome =:r
ORDER BY tb_empresas.em_nome

Answer 2

Staff managed to solve, follows below the query that solved my problem, I used the Euler01 query as a basis:

SELECT COUNT(tb_pessoas.ps_id) AS quantidade, tb_empresas.em_nome, tb_pessoas.ps_sexo
FROM tb_pessoas
INNER JOIN tb_empresas ON tb_empresas.em_id = tb_pessoas.em_id
WHERE TIMESTAMPDIFF(YEAR, ps_idade, NOW())
BETWEEN 29 AND 33 AND tb_pessoas.ps_sexo = "Masculino"
ORDER BY tb_empresas.em_nome =:r

Staff thank you very much to all for the help, were very helpful to me! It was my first post, I hope I can also contribute to the community with the little knowledge I have! Hug!