Select query by age group

Question

I made an appointment select thus:

SELECT 
data,
sexo,
idade,
count(*) qtd
FROM marcacao
group by data, sexo, idade

And return me a table similar to the table below:

DATA       | SEXO | IDADE | QTD
01/07/2017 |  F   |   30  |  3
01/07/2017 |  F   |   33  |  2
02/07/2017 |  F   |   45  |  4
03/07/2017 |  F   |   51  |  5

I want to make a select on the basis of select previous, and I need to filter by dates and by age group, it would be something +- like this:

SELECT (Select_anterior)
where data >= dataini and data <= dataini

And the result should come out by age group, ex:

FAIXA ETARIA   | SEXO | QTD
0  - 10        |  F   |  30
11 - 20        |  F   |  22
21 - 30        |  F   |  13
31 - 40        |  F   |  28

I do not know if it is possible to do something like this. I hope you have understood my doubt, but I can change the question if it is necessary.

Answer 1

You can do this using the IF, below an example:

SELECT IF(idade <= 10, '0 - 10', IF(idade <= 20, '11 - 20', IF(idade <= 30, '21 - 30', IF(idade <= 40, '31 - 40', 0)))) faixa_etaria, sexo, count(*) qtd,
FROM marcacao
GROUP BY faixa_etaria

In the comments of that reply you ask for the SELECT list all age groups, even if there is no one. To do this there are two alternatives.

The first requires a base table, follows an example:

CREATE TABLE `faixa_etaria`(
    `idade_limite` INT,
    `faixa_etaria` CHAR(10) DEFAULT ''
);

INSERT INTO `faixa_etaria` (`idade_limite`, `faixa_etaria`) VALUES
(10, '1 - 10'),
(20, '11 - 20'),
(30, '21 - 30'),
(40, '31 - 40');

And the SELECT be on this table making a INNER JOIN with your table marcacao, follows an example:

SELECT a.faixa_etaria, b.sexo, COUNT(a.idade) qtd,
FROM faixa_etaria a
LEFT JOIN marcacao b ON b.idade <= a.idade_limite
GROUP BY a.faixa_etaria

The second way to do this is by putting the data directly into your SELECT in this way:

SELECT a.faixa_etaria FROM (
    SELECT 10 idade_limite, '0 - 10' faixa_etaria
    UNION
    SELECT 20 idade_limite, '11 - 20' faixa_etaria
    UNION
    SELECT 30 idade_limite, '21 - 30' faixa_etaria
    UNION
    SELECT 40 idade_limite, '31 - 40' faixa_etaria
) a
LEFT JOIN marcacao b ON b.idade <= a.idade_limite
GROUP BY a.faixa_etaria;

Answer 2

Let’s assume you’re passing the dataini parameters, like:

$dataini = $_GET['dataini'];  

So the WHERE clauses should be:

SELECT 
    data,
    sexo,
    idade,
    count(*) qtd
    FROM marcacao
    WHERE
    (data >= '$dataini') and 
    (data <= '$dataini')
    group by data, sexo, idade

Answer 3

I believe you can make this filter and grouping using a "With" in Sqlserver:

WITH Sales_CTE (SalesPersonID, NumberOfOrders)  
AS  
(  
    SELECT SalesPersonID, COUNT(*)  
    FROM Sales.SalesOrderHeader  
    WHERE SalesPersonID IS NOT NULL  
    GROUP BY SalesPersonID  
)  
SELECT AVG(NumberOfOrders) AS "Average Sales Per Person"  
FROM Sales_CTE;  
GO

It would look something like this:

WITH TempTable (data, sexo, idade, qtd)  
AS  
(  
    SELECT 
        data,
        sexo,
        idade,
        count(*) qtd
    FROM marcacao
    GROUP BY data, sexo, idad
)  
SELECT [FormataçãoDoSeuResultado]   
FROM TempTable;  
GO

Answer 4

Try this way, you will get a list with: id, nome, idade, faixa inicial and faixa final. Make a view with this select, then just group by track.

SELECT
  can_id,
  can_nome,
  can_data_nacimento,
  (YEAR(CURDATE()) - YEAR(can_data_nacimento)) - (RIGHT(CURDATE(),5) < RIGHT(can_data_nacimento,5)) AS idade,
  faixa_inicial,
  faixa_final   
FROM
  candidato,
  faixa_etaria --> tabela de faixa
WHERE can_data_nacimento is not null   
  AND (YEAR(CURDATE()) - YEAR(can_data_nacimento)) - (RIGHT(CURDATE(),5) < RIGHT(can_data_nacimento,5))
  BETWEEN faixa_inicial AND faixa_final