0
I have a table called client with several customers already registered and I want to filter all customers who are the same age and get an entire number of these records, in case the amount of customers of the same age.
1 answer
3
I’m considering you have a table called Cliente, and in this table has a field called DataNasc.
The code below returns a list of all ages and the amount of records at that age:
declare @hoje as datetime = getdate()
select idade, count(*) as qtd_idade
from (
select CASE WHEN datepart(mm,DataNasc) > datepart(mm,@hoje) OR
(datepart(mm,DataNasc) = datepart(mm,@hoje)
AND datepart(dd,DataNasc) >= datepart(dd,@hoje))
THEN DATEDIFF(year, DataNasc, @hoje) - 1
ELSE DATEDIFF(year, DataNasc, @hoje)
end as idade
from Cliente
) as t
group by idade
Let me know if I’ve got it wrong.
Browser other questions tagged database sql-server table
You are not signed in. Login or sign up in order to post.
I don’t know how your table is, but I believe you can use COUNT in your query. https://docs.microsoft.com/en-us/sql/t-sql/functions/count-transact-sql?view=sql-server-2017
– Raquel Andrade
You need to define the problem better, the description is ambiguous. What is the same age? Compared to what? Depending on the description all customers are the same age as another customer unless he is the only one at that age, which is rare to happen on bases with an even small size, and even if it gives a small difference should be irrelevant.
– Maniero
And do you have what is recorded in your table the age or date of birth? If it is the date of birth you want to compare with the current date and what accuracy would you like to use to compare age equality, years? years/months? years/months/days? If you want to count customers of the same age then the result of this count will certainly be an integer.
– anonimo
@Raquelandrade Thanks, your comment helped me a lot. :)
– Gabriel Silva