Count number of people in a table separating by age, sex and company

Question

Count number of people in a table separating by age, sex and company

Navigation

#1 by (0 votes)
#2 by (0 votes)

0

I'm having trouble running a select where you have to bring the amount of people working in a company , separating by age strong>, age and sex .

I've done the example below, but so far I have not been able to make it work, it has an alias error.

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 by Personally the above query is working normally, but the way it is calculating the direct age (was keeping the age not the date of birth), I changed the field to date in the database and now I am saving the date of birth, I need which brings the amount of people separating by the company, age based on date of birth, compare the age range that I set and sex. I tried the query below but it is 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 look 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

mysql

asked by anonymous 01.06.2016 / 02:06

2 answers

0

I was able to solve the problem, I used the Euler01 query as a base:

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

Personal thanks to all for the help, they were very useful for me! It was my first post, I hope I can contribute to the community with the little knowledge I have! Hugs!

01.06.2016 / 22:55

Music player showing strange flaws. Am I making a mistake? I can not save a query in the database. Display errors

score 0 · Accepted Answer

To do this you have to add up the amount of people grouping 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