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:

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)
),
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",
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
``````