How to select columns in which years are equal in Oracle?

Question

I have a table called Clientes, and in it I have the following columns :

id_cliente
nm_cliente
dt_nascimento

I need to select and count all customers who were born in the same year. I have tried some things, but they do not return on the basis of the same year but of the same day, month and year.

My current query that is not working is like this :

SELECT dt_nascimento, count(*) FROM clientes
WHERE dt_nascimento = dt_nascimento
GROUP by dt_nascimento;

How can I select only based on the same year ?

Answer 1

You have to extract the year from the date field with the function Extract, can use this query:

SELECT  EXTRACT(year FROM dt_nascimento) as ano, count(*) as quantidade
FROM clientes
GROUP by EXTRACT(year FROM dt_nascimento);

See the result: