SQL query grouping by age group

Question

Hello, I need a way to make an sql query that brings me the amount of male and female people. OBS: I have two tables where the first one is registered in it I have the sex column and another table that would be faixaetaria only with the nomedaxaetaria. The registration table has a column called fok_faixaetaria which is a foreign key for the faixaetaria column. I would like to perform a consultation that brings quantity of male and female people by pheasant.

SELECT COUNT(case when sexo= 'Masculino' then 1 end) AS Masculino,
COUNT(case when sexo= 'Feminino' then 1 end) AS Feminino FROM cadastro
where dataInicial = '01/08/2015' and dataFinal = '31/08/2015' 

How can I make the appointment since I have 6 age group and would like to count each male and female by age group?

Answer 1

You should list the registration table with the age range table and group the results through the age range column.

Use the example below:

SELECT 
      COUNT(case when sexo= 'Masculino' then 1 end) AS Masculino,
      COUNT(case when sexo= 'Feminino' then 1 end) AS Feminino, 
      FE.FAIXAETARIA AS [Faixa Etaria]
FROM 
      cadastro c (nolock)
      LEFT JOIN FAIXAETARIA FE (NOLOCK) ON FE.FAIXAETARIA = C.FOK_FAIXAETARIA
WHERE 
      dataInicial = '01/08/2015' and dataFinal = '31/08/2015' 
GROUP BY
      FE.FAIXAETARIA
ORDER BY
      FE.FAIXAETARIA DESC