Select query by age group

2

I made a query select like this:

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

And I get 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 based on the select previous, being that I need to filter by dates and by age, it would be something + - like this:

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

And the result should be by age, eg:

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's possible to do something like this. I hope you have understood my question, but I can change the question if necessary.

    
asked by anonymous 24.07.2017 / 14:21

3 answers

3

You can do this using 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 for this answer, you ask SELECT to list all age groups, even if there is no one. To do this you have two alternatives.

The first one needs a base table, an example follows:

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 is on this table by making a INNER JOIN with its marcacao table, an example follows:

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 to put the data straight into your SELECT like this:

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;
    
24.07.2017 / 14:38
0

Let's suppose you're passing the dataini parameters, like:

$dataini = $_GET['dataini'];  

Then the WHERE clauses should be:

SELECT 
    data,
    sexo,
    idade,
    count(*) qtd
    FROM marcacao
    WHERE
    (data >= '$dataini') and 
    (data <= '$dataini')
    group by data, sexo, idade
    
24.07.2017 / 14:30
-1

I believe you can do this filtering 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
    
24.07.2017 / 14:35