UPDATED – How do I get an avg in a range of year

Issue

I have 2 tables attendance and people.

I need to get the AVG of attendance by range of year.

To do that I need get the column people.DT_NASCIMENTO_FUNDACAO (DATE) and attendance.DT_SOLICITACAO_ATENDIMENTO (DATE).

The result this query should be something like:

-------------------------
| 0 - 18 | 19 - 23| ... |
-------------------------
|  0.2   |  0.8   | ... |
-------------------------

where 0.2 and 0.8 is a decimal representation of fraction of attendance by range of year.

people have CD_PESSOA PK and attendance have NU_USUARIO LIKE FK.

 ______                           ____________
|PEOPLE| 1 ------------------> N | ATTENDANCE | 
 -------                           ------------ 

I created a code that count the numbers of time that a range of year happen:

SELECT
    SUM(CASE  
        WHEN EXTRACT(YEAR FROM TO_DATE(P.DT_NASCIMENTO_FUNDACAO)) 
        BETWEEN EXTRACT(YEAR FROM TO_DATE(G.DT_SOLICITACAO_ATENDIMENTO)) - 18 
        AND EXTRACT(YEAR FROM TO_DATE(G.DT_SOLICITACAO_ATENDIMENTO)) 
        THEN 1 ELSE 0 END) AS "0 - 18",
        
    SUM(CASE
        WHEN EXTRACT(YEAR FROM TO_DATE(P.DT_NASCIMENTO_FUNDACAO)) 
        BETWEEN EXTRACT(YEAR FROM TO_DATE(G.DT_SOLICITACAO_ATENDIMENTO)) - 23 
        AND EXTRACT(YEAR FROM TO_DATE(G.DT_SOLICITACAO_ATENDIMENTO)) - 19  
        THEN 1 ELSE 0 END) AS "19 - 23"
FROM ATTENDANCE G JOIN PEOPLE P
ON (G.NU_USUARIO = P.CD_PESSOA)
WHERE EXTRACT(YEAR FROM TO_DATE(G.DT_SOLICITACAO_ATENDIMENTO)) >= EXTRACT(YEAR FROM SYSDATE) - 1
AND EXTRACT(YEAR FROM TO_DATE(G.DT_SOLICITACAO_ATENDIMENTO)) <= EXTRACT(YEAR FROM SYSDATE);

As result I have:

enter image description here

Now, I need change this result to show me the percentual representation. How can I do that?

Here in this link I crate the sql file to create the tables and insert sample data(50 people and 2000 attendances).

Here we have some 50 sample data about born and 1000 attendance date.

people.DT_NASCIMENTO_FUNDACAO

01-07-2010
15-09-2014
10-07-1970
26-01-1989
02-05-1982
09-05-2009
25-08-2006
02-12-2013
08-04-2008
11-01-1962
01-05-1980
11-11-1993
15-07-2013
03-03-1990
09-10-2013
31-12-2008
25-07-2015
16-06-1972
03-11-1985
03-11-2006
19-03-1955
03-01-1974
08-01-2015
13-11-2003
07-01-1989
22-01-2007
30-11-1999
04-12-1970
24-05-1983
25-04-2012
11-11-1988
16-09-1980
21-05-1989
01-06-2011
14-09-1973
16-10-2015
10-02-2000
30-10-2004
27-02-1965
30-01-2015
23-05-1993
07-01-1987
04-03-1998
22-10-2011
17-12-1999
19-02-1955
21-07-1961

attendance.DT_SOLICITACAO_ATENDIMENTO

18-09-2020
21-09-2020
18-09-2020
18-09-2020
18-09-2020
18-09-2020
30-09-2020
30-09-2020
30-09-2020
14-09-2020
23-09-2020
18-09-2020
18-09-2020
18-09-2020
18-09-2020
08-09-2020
08-09-2020
08-09-2020
08-09-2020
08-09-2020
08-09-2020
08-09-2020
08-09-2020
18-09-2020
21-09-2020
21-09-2020
04-09-2020
04-09-2020
04-09-2020
04-09-2020
04-09-2020
08-09-2020
08-09-2020
08-09-2020
09-09-2020
25-08-2020
25-08-2020
25-08-2020
25-08-2020
01-09-2020
01-09-2020
01-09-2020
01-09-2020
01-09-2020
01-09-2020
01-09-2020
01-09-2020
01-09-2020
08-09-2020
17-09-2020
08-09-2020
08-09-2020
08-09-2020
17-09-2020
08-09-2020
08-09-2020
08-09-2020
08-09-2020
08-09-2020
08-09-2020
08-09-2020
08-09-2020
08-09-2020
08-09-2020
08-09-2020
21-09-2020
13-10-2020
04-09-2020
18-09-2020
18-09-2020
18-09-2020
18-09-2020
18-09-2020
18-09-2020
18-09-2020
18-09-2020
18-09-2020
18-09-2020
21-09-2020
21-09-2020
21-09-2020
18-09-2020
23-09-2020
18-09-2020
23-09-2020
18-09-2020
18-09-2020
18-09-2020
18-09-2020
18-09-2020
21-09-2020
21-09-2020
28-09-2020
28-09-2020
28-09-2020
28-09-2020
28-09-2020
28-09-2020
28-09-2020
29-09-2020
28-09-2020
28-09-2020
28-09-2020
28-09-2020
28-09-2020
28-09-2020
28-09-2020
28-09-2020
28-09-2020
28-09-2020
28-09-2020
28-09-2020
25-09-2020
25-09-2020
25-09-2020
25-09-2020
28-09-2020
24-09-2020
16-09-2020
16-09-2020
16-09-2020
16-09-2020
16-09-2020
16-09-2020
16-09-2020
16-09-2020
16-09-2020
16-09-2020
16-09-2020
16-09-2020
16-09-2020
16-09-2020
16-09-2020
16-09-2020
18-09-2020
03-08-2019
03-08-2019
03-08-2019
03-08-2019
02-08-2019
02-08-2019
02-08-2019
02-08-2019
09-08-2019
09-08-2019
09-08-2019
09-08-2019
09-08-2019
09-08-2019
09-08-2019
09-08-2019
09-08-2019
09-08-2019
09-08-2019
09-08-2019
09-08-2019
09-08-2019
09-08-2019
09-08-2019
09-08-2019
22-08-2019
22-08-2019
22-08-2019
22-08-2019
22-08-2019
22-08-2019
22-08-2019
22-08-2019
22-08-2019
22-08-2019
22-08-2019
22-08-2019
22-08-2019
22-08-2019
23-08-2019
23-08-2019
23-08-2019
23-08-2019
23-08-2019
23-08-2019
23-08-2019
23-08-2019
23-08-2019
23-08-2019
23-08-2019
23-08-2019
23-08-2019
23-08-2019
23-08-2019
23-08-2019
30-08-2019
30-08-2019
30-08-2019
30-08-2019
30-08-2019
30-08-2019
30-08-2019
30-08-2019
30-08-2019
30-08-2019
30-08-2019
30-08-2019
30-08-2019
30-08-2019
30-08-2019
30-08-2019
30-08-2019
30-08-2019
30-08-2019
30-08-2019
30-08-2019
30-08-2019
30-08-2019
27-08-2019
27-08-2019
27-08-2019
27-08-2019
27-08-2019
27-08-2019
27-08-2019
21-08-2019
21-08-2019
21-08-2019
21-08-2019
21-08-2019
21-08-2019
21-08-2019
21-08-2019
21-08-2019
21-08-2019
21-08-2019
21-08-2019
21-08-2019
21-08-2019
21-08-2019
21-08-2019
21-08-2019
21-08-2019
27-08-2019
27-08-2019
27-08-2019
27-08-2019
27-08-2019
27-08-2019
27-08-2019
27-08-2019
27-08-2019
27-08-2019
27-08-2019
27-08-2019
21-08-2019
21-08-2019
21-08-2019
21-08-2019
21-08-2019
21-08-2019
21-08-2019
21-08-2019
21-08-2019
21-08-2019
21-08-2019
21-08-2019
21-08-2019
21-08-2019
21-08-2019
21-08-2019
21-08-2019
21-08-2019
21-08-2019
21-08-2019
09-08-2019
06-08-2019
17-07-2019
17-10-2020
17-10-2020
18-10-2020
19-10-2020
19-10-2020
19-10-2020
19-10-2020
19-10-2020
31-10-2020
31-10-2020
31-10-2020
31-10-2020
31-10-2020
31-10-2020
31-10-2020
31-10-2020
24-10-2020
24-10-2020
24-10-2020
24-10-2020
24-10-2020
24-10-2020
24-10-2020
24-10-2020
24-10-2020
24-10-2020
24-10-2020
24-10-2020
26-10-2020
26-10-2020
26-10-2020
26-10-2020
26-10-2020
26-10-2020
26-10-2020
26-10-2020
26-10-2020
26-10-2020
29-10-2020
29-10-2020
26-10-2020
26-10-2020
26-10-2020
26-10-2020
28-10-2020
28-10-2020
10-10-2020
10-10-2020
10-10-2020
10-10-2020
08-10-2020
08-10-2020
08-10-2020
08-10-2020
11-10-2020
11-10-2020
13-10-2020
13-10-2020
14-10-2020
14-10-2020
17-10-2020
17-10-2020
17-10-2020
16-10-2020
18-10-2020
18-10-2020
04-10-2020
04-10-2020
13-10-2020
13-10-2020
13-10-2020
09-10-2020
13-10-2020
09-10-2020
14-10-2020
14-10-2020
01-10-2020
01-10-2020
01-10-2020
01-10-2020
01-10-2020
01-10-2020
01-10-2020
01-10-2020
01-10-2020
01-10-2020
01-10-2020
01-10-2020
01-10-2020
01-10-2020
01-10-2020
01-10-2020
01-10-2020
01-10-2020
01-10-2020
01-10-2020
01-10-2020
01-10-2020
26-10-2020
27-10-2020
27-10-2020
27-10-2020
27-10-2020
27-10-2020
27-10-2020
27-10-2020
27-10-2020
27-10-2020
27-10-2020
27-10-2020
27-10-2020
27-10-2020
27-10-2020
27-10-2020
27-10-2020
27-10-2020
27-10-2020
27-10-2020
27-10-2020
27-10-2020
27-10-2020
27-10-2020
27-10-2020
27-10-2020
07-10-2020
12-10-2020
10-10-2020
16-10-2020
16-10-2020
15-10-2020
10-09-2020
10-09-2020
10-09-2020
09-03-2020
09-03-2020
11-09-2020
11-09-2020
11-09-2020
27-06-2019
27-06-2019
27-06-2019
27-06-2019
27-06-2019
27-06-2019
27-06-2019
27-06-2019
27-06-2019
27-06-2019
27-06-2019
27-06-2019
27-06-2019
27-06-2019
19-06-2019
19-06-2019
19-06-2019
19-06-2019
19-06-2019
19-06-2019
19-06-2019
19-06-2019
19-06-2019
20-06-2019
20-06-2019
20-06-2019
20-06-2019
20-06-2019
20-06-2019
28-06-2019
28-06-2019
28-06-2019
28-06-2019
28-06-2019
28-06-2019
28-06-2019
28-06-2019
28-06-2019
28-06-2019
28-06-2019
28-06-2019
28-06-2019
28-06-2019
28-06-2019
30-06-2019
30-06-2019
30-06-2019
30-06-2019
30-06-2019
30-06-2019
30-06-2019
30-06-2019
30-06-2019
30-06-2019
30-06-2019
30-06-2019
30-06-2019
30-06-2019
30-06-2019
30-06-2019
30-06-2019
30-06-2019
30-06-2019
30-06-2019
30-06-2019
30-06-2019
30-06-2019
30-06-2019
30-06-2019
30-06-2019
30-06-2019
30-06-2019
30-06-2019
30-06-2019
03-06-2019
03-06-2019
26-06-2019
26-06-2019
26-06-2019
26-06-2019
26-06-2019
27-06-2019
27-06-2019
27-06-2019
27-06-2019
27-06-2019
27-06-2019
27-06-2019
27-06-2019
27-06-2019
27-06-2019
27-06-2019
27-06-2019
27-06-2019
27-06-2019
27-06-2019
27-06-2019
27-06-2019
27-06-2019
27-06-2019
27-06-2019
27-06-2019
27-06-2019
27-06-2019
27-06-2019
27-06-2019
27-06-2019
27-06-2019
27-06-2019
27-06-2019
14-06-2019
14-06-2019
29-06-2019
29-06-2019
29-06-2019
29-06-2019
29-06-2019
29-06-2019
29-06-2019
29-06-2019
29-06-2019
29-06-2019
29-06-2019
30-06-2019
30-06-2019
30-06-2019
30-06-2019
30-06-2019
30-06-2019
30-06-2019
30-06-2019
30-06-2019
30-06-2019
30-06-2019
30-06-2019
21-08-2021
21-08-2021
21-08-2021
21-08-2021
21-08-2021
21-08-2021
21-08-2021
21-08-2021
21-08-2021
21-08-2021
21-08-2021
21-08-2021
21-08-2021
21-08-2021
21-08-2021
21-08-2021
21-08-2021
21-08-2021
21-08-2021
21-08-2021
21-08-2021
21-08-2021
21-08-2021
21-08-2021
21-08-2021
21-08-2021
21-08-2021
21-08-2021
21-08-2021
21-08-2021
21-08-2021
21-08-2021
21-08-2021
21-08-2021
21-08-2021
21-08-2021
21-08-2021
19-08-2021
19-08-2021
19-08-2021
19-08-2021
19-08-2021
19-08-2021
19-08-2021
19-08-2021
19-08-2021
19-08-2021
19-08-2021
19-08-2021
19-08-2021
19-08-2021
19-08-2021
19-08-2021
19-08-2021
19-08-2021
19-08-2021
19-08-2021
19-08-2021
19-08-2021
19-08-2021
19-08-2021
19-08-2021
19-08-2021
19-08-2021
19-08-2021
19-08-2021
19-08-2021
19-08-2021
19-08-2021
19-08-2021
19-08-2021
19-08-2021
19-08-2021
19-08-2021
19-08-2021
11-08-2021
11-08-2021
11-08-2021
11-08-2021
11-08-2021
11-08-2021
11-08-2021
11-08-2021
11-08-2021
11-08-2021
11-08-2021
11-08-2021
12-08-2021
12-08-2021
12-08-2021
12-08-2021
12-08-2021
19-08-2021
19-08-2021
19-08-2021
19-08-2021
18-08-2021
18-08-2021
20-08-2021
20-08-2021
20-08-2021
20-08-2021
20-08-2021
20-08-2021
20-08-2021
20-08-2021
20-08-2021
20-08-2021
20-08-2021
20-08-2021
20-08-2021
20-08-2021
20-08-2021
20-08-2021
20-08-2021
20-08-2021
19-08-2021
19-08-2021
19-08-2021
18-08-2021
18-08-2021
18-08-2021
18-08-2021
18-08-2021
18-08-2021
18-08-2021
18-08-2021
18-08-2021
18-08-2021
19-08-2021
19-08-2021
19-08-2021
19-08-2021
19-08-2021
19-08-2021
19-08-2021
30-11-2019
30-11-2019
30-11-2019
30-11-2019
30-11-2019
30-11-2019
11-11-2019
30-11-2019
27-11-2019
06-11-2019
06-11-2019
06-11-2019
06-11-2019
06-11-2019
06-11-2019
06-11-2019
07-11-2019
07-11-2019
07-11-2019
07-11-2019
07-11-2019
07-11-2019
07-11-2019
08-11-2019
08-11-2019
08-11-2019
08-11-2019
08-11-2019
08-11-2019
08-11-2019
08-11-2019
08-11-2019
11-11-2019
11-11-2019
12-11-2019
12-11-2019
12-11-2019
12-11-2019
30-10-2019
07-11-2019
10-12-2019
10-12-2019
10-12-2019
10-12-2019
10-12-2019
10-12-2019
10-12-2019
10-12-2019
10-12-2019
10-12-2019
10-12-2019
10-12-2019
10-12-2019
10-12-2019
10-12-2019
10-12-2019
10-12-2019
10-12-2019
10-12-2019
10-12-2019
10-12-2019
10-12-2019
10-12-2019
10-12-2019
10-12-2019
10-12-2019
10-12-2019
10-12-2019
10-12-2019
10-12-2019
28-11-2019
28-11-2019
28-11-2019
28-11-2019
28-11-2019
28-11-2019
28-11-2019
04-11-2019
04-11-2019
04-11-2019
04-11-2019
04-11-2019
25-11-2019
25-11-2019
25-11-2019
25-11-2019
25-11-2019
25-11-2019
25-11-2019
25-11-2019
26-11-2019
27-11-2019
27-11-2019
27-11-2019
27-11-2019
27-11-2019
27-11-2019
27-11-2019
27-11-2019
27-11-2019
27-11-2019
27-11-2019
27-11-2019
27-11-2019
27-11-2019
27-11-2019
28-11-2019
03-12-2019
06-11-2019
07-11-2019
07-11-2019
12-11-2019
26-11-2019
28-11-2019
11-11-2019
12-11-2019
12-11-2019
25-11-2019
02-12-2019
13-11-2019
28-11-2019
07-11-2019
07-11-2019
07-11-2019
01-11-2019
01-11-2019
01-11-2019
01-11-2019
01-11-2019
01-11-2019
04-11-2019
04-11-2019
07-11-2019
07-11-2019
25-11-2019
14-03-2019
14-03-2019
20-03-2019
20-03-2019
20-03-2019
20-03-2019
20-03-2019
20-03-2019
20-03-2019
20-03-2019
20-03-2019
18-03-2019
14-03-2019
14-03-2019
14-03-2019
14-03-2019
14-03-2019
14-03-2019
14-03-2019
18-03-2019
18-03-2019
18-03-2019
18-03-2019
14-03-2019
15-03-2019
15-03-2019
15-03-2019
15-03-2019
15-03-2019
15-03-2019
15-03-2019
15-03-2019
15-03-2019
15-03-2019
15-03-2019
15-03-2019
15-03-2019
15-03-2019
15-03-2019
15-03-2019
18-03-2019
01-03-2019
01-03-2019
01-03-2019
07-03-2019
07-03-2019
07-03-2019
07-03-2019
07-03-2019
07-03-2019
18-03-2019
18-03-2019
18-03-2019
22-03-2019
22-03-2019
22-03-2019
22-03-2019
22-03-2019
22-03-2019
22-03-2019
22-03-2019
22-03-2019
22-03-2019
22-03-2019
22-03-2019
22-03-2019
22-03-2019
22-03-2019
22-03-2019
22-03-2019
22-03-2019
22-03-2019
22-03-2019
22-03-2019
22-03-2019
22-03-2019
22-03-2019
22-03-2019
14-03-2019
14-03-2019
14-03-2019
14-03-2019
14-03-2019
19-03-2019
15-01-2019
05-02-2019
20-03-2019
27-12-2018
13-02-2019
13-02-2019
13-02-2019
13-02-2019
29-01-2019
13-11-2018
22-03-2019
12-03-2019
13-02-2019
13-02-2019
17-01-2019
25-03-2019
21-03-2019
21-02-2019
26-03-2019
22-02-2019
22-02-2019
22-02-2019
22-02-2019
07-03-2019
07-03-2019
07-03-2019
07-03-2019
07-03-2019
25-03-2019
07-02-2019
07-02-2019
28-02-2019
28-02-2019
28-02-2019
28-02-2019
28-02-2019
28-02-2019
13-03-2019
13-03-2019
13-03-2019
13-03-2019
13-03-2019
13-03-2019
13-03-2019
13-03-2019
13-03-2019
13-03-2019
13-03-2019
13-03-2019
13-03-2019
13-03-2019
13-03-2019
13-03-2019
13-03-2019
05-07-2019
08-07-2019
08-07-2019
08-07-2019
08-07-2019
08-07-2019
08-07-2019
08-07-2019
08-07-2019
08-07-2019
08-07-2019
08-07-2019
08-07-2019
08-07-2019
08-07-2019
05-07-2019
05-07-2019
05-07-2019
05-07-2019
05-07-2019
05-07-2019
05-07-2019
05-07-2019
05-07-2019
05-07-2019
05-07-2019
11-07-2019
11-07-2019
12-07-2019
12-07-2019
12-07-2019
12-07-2019
12-07-2019
12-07-2019
12-07-2019
12-07-2019
12-07-2019
11-07-2019
11-07-2019
11-07-2019
09-07-2019
11-07-2019
11-07-2019
11-07-2019
11-07-2019

How can I do that?

Solution

To solve my problem was necessary a group operation on database, incluse to create 2 temporary tables.

The solution to my case was:

WITH TABLE_X AS (
    SELECT P.CD_PESSOA, P.DT_NASCIMENTO_FUNDACAO, TO_DATE(SYSDATE) AS DATA_ATUAL, TO_DATE(G.DT_SOLICITACAO_ATENDIMENTO), 
    EXTRACT(YEAR FROM SYSDATE) - EXTRACT(YEAR FROM TO_DATE(P.DT_NASCIMENTO_FUNDACAO)) AS IDADE
    FROM ATTENDANCE G JOIN PEOPLE P
    ON (G.NU_USUARIO = P.CD_PESSOA)
    WHERE EXTRACT(YEAR FROM TO_DATE(G.DT_SOLICITACAO_ATENDIMENTO)) >= EXTRACT(YEAR FROM SYSDATE) - 1
    AND EXTRACT(YEAR FROM TO_DATE(G.DT_SOLICITACAO_ATENDIMENTO)) <= EXTRACT(YEAR FROM SYSDATE)
    ORDER BY IDADE DESC
),
TABLE_Y AS (SELECT  
    SUM(CASE WHEN IDADE BETWEEN 0 AND 18 THEN 1 ELSE 0 END) AS "0-18",
    SUM(CASE WHEN IDADE BETWEEN 19 AND 23 THEN 1 ELSE 0 END) AS "19-23",
    SUM(CASE WHEN IDADE BETWEEN 24 AND 28 THEN 1 ELSE 0 END) AS "24-28",
    SUM(CASE WHEN IDADE BETWEEN 29 AND 33 THEN 1 ELSE 0 END) AS "29-33",
    SUM(CASE WHEN IDADE BETWEEN 34 AND 38 THEN 1 ELSE 0 END) AS "34-38",
    SUM(CASE WHEN IDADE BETWEEN 39 AND 43 THEN 1 ELSE 0 END) AS "39-43",
    SUM(CASE WHEN IDADE BETWEEN 44 AND 48 THEN 1 ELSE 0 END) AS "44-48",
    SUM(CASE WHEN IDADE BETWEEN 49 AND 53 THEN 1 ELSE 0 END) AS "49-53",
    SUM(CASE WHEN IDADE BETWEEN 54 AND 58 THEN 1 ELSE 0 END) AS "54-58",
    SUM(CASE WHEN IDADE >= 59 THEN 1 ELSE 0 END) AS "59+",
    SUM(CASE WHEN IDADE IS NULL THEN 1 ELSE 0 END) AS "VALORES_NULL",
    COUNT(IDADE) AS TOTAL_IDADE
FROM TABLE_X
)
SELECT 
    ROUND(("0-18" / TOTAL_IDADE) * 100, 2) ||'%' AS "0-18", 
    ROUND(("19-23" / TOTAL_IDADE) * 100, 2) ||'%' AS "19-23",
    ROUND(("24-28" / TOTAL_IDADE) * 100, 2) ||'%' AS "24-28", 
    ROUND(("29-33" / TOTAL_IDADE) * 100, 2) ||'%' AS "29-33", 
    ROUND(("34-38" / TOTAL_IDADE) * 100, 2) ||'%' AS "29-33", 
    ROUND(("39-43" / TOTAL_IDADE) * 100, 2) ||'%' AS "34-38", 
    ROUND(("44-48" / TOTAL_IDADE) * 100, 2) ||'%' AS "44-48", 
    ROUND(("49-53" / TOTAL_IDADE) * 100, 2) ||'%' AS "49-53", 
    ROUND(("54-58" / TOTAL_IDADE) * 100, 2) ||'%' AS "54-58", 
    ROUND(("59+" / TOTAL_IDADE) * 100, 2) ||'%' AS "59+"
FROM TABLE_Y

As result I had:

enter image description here

Answered By – Eric Gomes

Answer Checked By – Marie Seifert (AngularFixing Admin)

Leave a Reply

Your email address will not be published.