Having issues using 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

SQL Server 2008 Forums

Transact-SQL (2008)

Having issues using GROUP BY

Author

Topic

byarian
Starting Member

1 Post

Posted - 2012-06-20 : 15:29:55

I need to show a count of how many student have the same phone number. If more than one student share a phone #, I need a count of how many and then I need the birthdate and ID for the youngest. This is where it is getting tricky. I need to group since I am ussing an aggregate function, but I do not want to group the Student_ID. I have racked my brain on this. Any ideas.

Phone# Birthday Student_ID
0123456789 2004-10-04 001
1234567890 1994-07-29 002
2345678901 1995-02-06 003
3456789012 2007-07-11 004
3456789012 2008-06-20 005
3456789012 2005-11-15 006

I need it to look like:

Phone# Count Birthday Student_ID
0123456789 1 2004-10-04 001
1234567890 1 1994-07-29 002
2345678901 1 1995-02-06 003
3456789012 3 2008-06-20 005

Here is the code I have so far, I just need to add the Student_ID.
[code SELECT

tblContact.HomePhone,
count(tblStudent.StudentNumber) as StudentNumber,
max(tblStudent.Birthdate) as Birthdate,
(select tblStudent.StudentNumber from tblStudent ts
join dbo.tblContact as tc ON ts.PrimaryContactId = tc.ContactId
where ts.Birthdate = ts.Birthdate)
FROM dbo.tblStudent as tblStudent INNER JOIN
dbo.tblStudentAnnual as tblStudentAnnual ON tblStudent.StudentId = tblStudentAnnual.StudentId INNER JOIN
dbo.tblSchoolYear as tblSchoolYear ON tblStudentAnnual.SchoolYearId = tblSchoolYear.SchoolYearId INNER JOIN
dbo.tblSchool as tblSchool ON tblStudentAnnual.SchoolId = tblSchool.SchoolId INNER JOIN
dbo.tblContact as tblContact ON tblStudent.PrimaryContactId = tblContact.ContactId INNER JOIN
dbo.tblCode as tblCode ON tblStudentAnnual.StudentStatusId = tblCode.CodeId
WHERE (tblStudentAnnual.SchoolYearId = dbo.DefaultSchoolYearId()) AND (tblCode.Flags = 4) AND (tblSchool.Code LIKE 'CH%')
Group by tblContact.HomePhone
ORDER BY HomePhone

SELECT

tblContact.HomePhone,
count(tblStudent.StudentNumber) as StudentNumber,
max(tblStudent.Birthdate) as Birthdate

FROM dbo.tblStudent as tblStudent INNER JOIN
dbo.tblStudentAnnual as tblStudentAnnual ON tblStudent.StudentId = tblStudentAnnual.StudentId INNER JOIN
dbo.tblSchoolYear as tblSchoolYear ON tblStudentAnnual.SchoolYearId = tblSchoolYear.SchoolYearId INNER JOIN
dbo.tblSchool as tblSchool ON tblStudentAnnual.SchoolId = tblSchool.SchoolId INNER JOIN
dbo.tblContact as tblContact ON tblStudent.PrimaryContactId = tblContact.ContactId INNER JOIN
dbo.tblCode as tblCode ON tblStudentAnnual.StudentStatusId = tblCode.CodeId
WHERE (tblStudentAnnual.SchoolYearId = dbo.DefaultSchoolYearId()) AND (tblCode.Flags = 4) AND (tblSchool.Code LIKE 'CH%')
Group by tblContact.HomePhone
ORDER BY HomePhone

[/code]

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2012-06-20 : 17:33:59

Here is one way:

DECLARE @T TABLE(Phone BIGINT, Birthday DATE, Student_ID INT)
INSERT @T
VALUES
(0123456789, '2004-10-04' ,001),
(1234567890, '1994-07-29' ,002),
(2345678901, '1995-02-06' ,003),
(3456789012, '2007-07-11' ,004),
(3456789012, '2008-06-20' ,005),
(3456789012, '2005-11-15' ,006)


SELECT *
FROM
(
	SELECT 
		Phone, 
		BirthDay,
		Student_ID,
		COUNT(Phone) OVER(PARTITION BY Phone) AS [Count], 
		ROW_NUMBER() OVER (PARTITION BY Phone ORDER BY BirthDay ASC) AS RowNum
	FROM @T
) AS A
WHERE
	RowNum = 1

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2012-06-20 : 17:37:32

Here is another using a group by clause:

SELECT 
	T.*,
	A.[Count]
FROM
	@T T
INNER JOIN
	(
		SELECT 
			Phone, 
			MIN(BirthDay) AS Birthday,
			COUNT(Phone) [Count]
		FROM	
			@T
		GROUP BY
			Phone
	) AS A
	ON T.Phone = A.Phone
	AND T.Birthday = A.Birthday

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources