CODEWITHSUNDEEP
sqldatediff
CompilerSaysNo
CompilerSaysNo

Reputation: 415

DATEDIFF and COUNT

I would like to count students by their current age for all students registered since 2010.

i.e.

16 - 2
17 - 5
19 - 5

In the current "student" table, I have the student's DOB and registration_date. I am using Management Studio.

So far I have: 

SELECT COUNT (*) 
FROM db.student 
WHERE DATEDIFF(year, DOB, CURRENT_TIMESTAMP) AND registration_date >= '2010-01-01'

but am not sure where to go from here.

Thank you in advance.

Upvotes: 1

Views: 129

Answers (3)

Sunny
Sunny

Reputation: 219

try this.

select DATEDIFF(Year,DOB,CURRENT_TIMESTAMP) age,Count(ID) users from dbo.student  where DatePart(year,registration_date)>=2010
GROUP BY  DATEDIFF(Year,DOB,CURRENT_TIMESTAMP)

Upvotes: 1

SQLDiver
SQLDiver

Reputation: 2018

To get the exact age, you need to take into account years, months and days.

To get a counts of students, grouped by age in years, your code would be:

SELECT
   Datediff(yyyy,DOB, CURRENT_TIMESTAMP)
    +   Case 
            When
            (
                    Datepart(mm,DOB) > Datepart(mm,CURRENT_TIMESTAMP)
                Or  (
                            Datepart(mm,DOB) = Datepart(mm,CURRENT_TIMESTAMP)
                        And Datepart(dd,DOB) > Datepart(dd,CURRENT_TIMESTAMP) 
                    )
            )
            Then -1
            Else 0
        End As Age,
   Count(*)
FROM
   db.student 
WHERE
   registration_date >= '2010-01-01'    SELECT
   Datediff(yyyy,DOB, CURRENT_TIMESTAMP)
    +   Case 
            When
            (
                    Datepart(mm,DOB) > Datepart(mm,CURRENT_TIMESTAMP)
                Or  (
                            Datepart(mm,DOB) = Datepart(mm,CURRENT_TIMESTAMP)
                        And Datepart(dd,DOB) > Datepart(dd,CURRENT_TIMESTAMP) 
                    )
            )
            Then -1
            Else 0
        End As Age,
   Count(*)
FROM
   db.student 
WHERE
   registration_date >= '2010-01-01'
GROUP BY
   Datediff(yyyy,DOB, CURRENT_TIMESTAMP)
    +   Case 
            When
            (
                    Datepart(mm,DOB) > Datepart(mm,CURRENT_TIMESTAMP)
                Or  (
                            Datepart(mm,DOB) = Datepart(mm,CURRENT_TIMESTAMP)
                        And Datepart(dd,DOB) > Datepart(dd,CURRENT_TIMESTAMP) 
                    )
            )
            Then -1
            Else 0
        End

Upvotes: 0

JustAPup
JustAPup

Reputation: 1780

You might need a GROUP BY. Probably something like this?

SELECT DATEDIFF(year, DOB, CURRENT_TIMESTAMP), COUNT (*) FROM db.student 
WHERE registration_date >= '2010-01-01'
GROUP BY DATEDIFF(year, DOB, CURRENT_TIMESTAMP)

Upvotes: 0

Related Questions