How to find age and group by - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

How to find age and group by

Author

Topic

Mohamed Faisal
Yak Posting Veteran

51 Posts

Posted - 2013-08-04 : 12:45:20

Hi,

I have created a table:
CREATE TABLE [dbo].[Student](
[StudentNumber] [varchar](50) NOT NULL,
[Name] [char](50) NOT NULL,
[Contact] [int] NOT NULL,
[Address] [char](50) NOT NULL,
[DateOfBirth] [datetime] NOT NULL,
[YearEnrolled] [int] NOT NULL,
[Year] [int] NOT NULL;
And insert the following data:
INSERT INTO Student VALUES('IT123456X', 'Ahmad Adam','05-18-1997', '33 Mangis Rod', 19970518, 2013, 1);
INSERT INTO Student VALUES('IT334455U', 'Mary Tan', '01-23-1996', '51 Koon Seng Road', 23-01-1996, 2012, 1);
INSERT INTO Student VALUES('BS123456X', 'Samuel Lee', '03-30-1997', '2 Joo Chiat Lane', 30-03-1997, 2013, 1);
INSERT INTO Student VALUES('BS234234Z', 'Nathaniel Koh', '12-08-1997', '5 Stll Road', 08-12-1997, 2013, 1);
INSERT INTO Student VALUES('BS987987F', 'Siti Faridah', '07-04-1995', '3 Duku Road', 04-07-1995, 2011, 3)

How do i caculate the age and how can i group by the StudentNumber like 'IT%'

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-08-04 : 21:34:34

Your insert statements don't seem to match the table. In any case, to find the age as of now, you would do something like this:

SELECT
   [StudentNumber], [Name],
   (
      CAST(CONVERT(CHAR(8), GETDATE(), 112) AS INT)
      - CAST(CONVERT(CHAR(8), [DateOfBirth], 112) AS INT)
   )/10000 As AGE
FROM
   dbo.Student

If you want to get only students with student number like IT%, add a where clause like this:

WHERE
   StudentNumber LIKE 'IT%'

jethrow
Starting Member

37 Posts

Posted - 2013-08-05 : 00:58:11

Is the Contact field supposed to be the same date as the DateOfBirth? Anyways, as James K pointed out, your insert statements seem a little off. Here is a working example with notes on where I modified the table:

declare @Student TABLE (
	[StudentNumber] [varchar](50) NOT NULL,
	[Name] [char](50) NOT NULL,
	[Contact] [date] NOT NULL, -- changed int to date
	[Address] [char](50) NOT NULL,
	[DateOfBirth] [varchar](20) NOT NULL, -- changed datetime to varchar
	[YearEnrolled] [int] NOT NULL,
	[Year] [int] NOT NULL)

INSERT INTO @Student VALUES -- added single quotes around DateOfBirth entries
('IT123456X', 'Ahmad Adam','05-18-1997', '33 Mangis Rod', '18-05-1997', 2013, 1), -- modified DateOfBirth format
('IT334455U', 'Mary Tan', '01-23-1996', '51 Koon Seng Road', '23-01-1996', 2012, 1),
('BS123456X', 'Samuel Lee', '03-30-1997', '2 Joo Chiat Lane', '30-03-1997', 2013, 1),
('BS234234Z', 'Nathaniel Koh', '12-08-1997', '5 Stll Road', '08-12-1997', 2013, 1),
('BS987987F', 'Siti Faridah', '07-04-1995', '3 Duku Road', '04-07-1995', 2011, 3);

Select StudentNumber, Name, DateDiff(year, Contact, GetDate()) Age From @Student
--Where StudentNumber Like 'IT%';

EDIT - yep, I sure forgot to check if they've had their birthday yet this year (see visakh16's response below). My Select statement should be:

Select StudentNumber, Name,
Case When DatePart(dy,Contact)<DatePart(dy,GetDate()) Then DateDiff(yy, Contact, GetDate())
     Else DateDiff(yy, Contact, GetDate())-1
End Age
From @Student

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-08-05 : 02:31:47

To get age


SELECT *,
CASE WHEN MONTH(DateOfBirth) < MONTH(GETDATE()) THEN DATEDIFF(yy,DateOfBirth,GETDATE())
     ELSE DATEDIFF(yy,DateOfBirth,GETDATE())-1
END AS [Age]
FROM @Student

to group on first part category use


SELECT LEFT(StudentNumber,PATINDEX('%[0-9]%',StudentNumber)-1),
AVG(CASE WHEN MONTH(DateOfBirth) < MONTH(GETDATE()) THEN DATEDIFF(yy,DateOfBirth,GETDATE())
     ELSE DATEDIFF(yy,DateOfBirth,GETDATE())-1
END * 1.0) AS AvgAge,
other fields...
FROM @Student
GROUP BY LEFT(StudentNumber,PATINDEX('%[0-9]%',StudentNumber)-1)

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources