grouping my result set based on the firstname column with all other columns included

Question

grouping my result set based on the firstname column with all other columns included

klineandking

SSCrazy

Points: 2570
More actions
January 27, 2010 at 6:46 am

#138411

good morning guys, i have a question here is the structure of the table and sample data
USE [Customers]
GO
/****** Object: Table [dbo].[duplicate2] Script Date: 01/27/2010 05:29:23 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[duplicate2](
[id] [int] IDENTITY(1,1) NOT NULL,
[firstName] [varchar](25) NULL,
[Lastname] [varchar](25) NULL,
[grades] [smallint] NULL,
[date] [datetime] NULL
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
INSERT INTO duplicate2
VALUES ('Nicholas','Edet',95,'2009-12-22')
GO
INSERT INTO duplicate2
VALUES ('Nicholas','Edet',76,'2009-12-22')
GO
INSERT INTO duplicate2
VALUES ('Emanuella','Edet',76,'2009-12-22')
GO
INSERT INTO duplicate2
VALUES ('Emanuella','Edet',80,'2009-12-22')
GO
INSERT INTO duplicate2
VALUES ('Emanuella','Edet',97,'2009-12-22')
GO
INSERT INTO duplicate2
VALUES ('Emanuella','Edet',76,'2009-12-20')
GO
INSERT INTO duplicate2
VALUES ('Nicholas','Edet',95,'2009-12-22')
GO
INSERT INTO duplicate2
VALUES ('jackson','Devo',70,'2009-12-21' )
GO
INSERT INTO duplicate2
VALUES ('Sarah','Palin',79,'2009-12-21 ')
GO
the question is ;
the question is i want to find the average grades based on the name and i want the result to include all other columns, the problem i have is that when i execute this statements;
select firstname, avg(grades)
from duplicate2
group by firstname
it groups the results based on firstname, but i want all the other columns to also
be included in the result set, but when i put all the other columns in the select statement
it does not group the results , can someone help me out.what i want to achieve is the result of the above select statement in the select statement below;
select firstname, lastname, date,avg(grades)
from duplicate2
group by firstname, lastname,date

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

LutzM SSC Guru Points: 107049 More actions · Answer 1

If you want all the other columns to be in there as well, how would your expected output look like?

Either you have one row per firstName then you'd have to decide which of the remaining values should be part of the result set or you want all data to be included, then you'll end up with multiple rows per firstName including avg(grades) to be displayed multiple times (oncer per row).

Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question[/url]
How to post performance related questions[/url]
Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 2

klineandking (1/27/2010)
VALUES ('Emanuella','Edet',97,'2009-12-22')
VALUES ('Emanuella','Edet',76,'2009-12-20')
the question is ;
the question is i want to find the average grades based on the name and i want the result to include all other columns, the problem i have is that when i execute this statements;
select firstname, avg(grades)
from duplicate2
group by firstname
it groups the results based on firstname, but i want all the other columns to also
be included in the result set, but when i put all the other columns in the select statement
it does not group the results , can someone help me out.what i want to achieve is the result of the above select statement in the select statement below;
select firstname, lastname, date,avg(grades)
from duplicate2
group by firstname, lastname,date

Which date? '2009-12-22' or '2009-12-20'? What are the business rules for deciding which of a number of dates should be chosen?

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

klineandking SSCrazy Points: 2570 More actions · Answer 3

i dont know how alll i want is the result set to grouped on the names,i know its difficult i cannot come up with the solution myself thats why i am on here, any help is very welcome i was thinking of using a CTE on this but i dont know if ll get the result have not tried it yet

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 4

klineandking (1/27/2010)
i dont know how alll i want is the result set to grouped on the names,i know its difficult i cannot come up with the solution myself thats why i am on here, any help is very welcome i was thinking of using a CTE on this but i dont know if ll get the result have not tried it yet

No, it's trivial. The hard part is figuring out what you want. This gives you everything so you can decide:

-- Make some sample data

DROP TABLE #duplicate2

CREATE TABLE #duplicate2(

[id] [int] IDENTITY(1,1) NOT NULL,

[firstName] [varchar](25) NULL,

[Lastname] [varchar](25) NULL,

[grades] [smallint] NULL,

[date] [datetime] NULL

) ON [PRIMARY]

INSERT INTO #duplicate2 ([firstName], [Lastname], [grades], [date])

SELECT 'Nicholas', 'Edet', 95, '2009-12-22' UNION ALL

SELECT 'Nicholas', 'Edet', 76, '2009-12-22' UNION ALL

SELECT 'Emanuella' ,'Edet', 76 ,'2009-12-22' UNION ALL

SELECT 'Emanuella', 'Edet', 80 ,'2009-12-22' UNION ALL

SELECT 'Emanuella', 'Edet', 97, '2009-12-22' UNION ALL

SELECT 'Emanuella', 'Edet', 76, '2009-12-20' UNION ALL

SELECT 'Nicholas', 'Edet', 95, '2009-12-22' UNION ALL

SELECT 'jackson',' Devo', 70 ,'2009-12-21' UNION ALL

SELECT 'Sarah',' Palin', 79 ,'2009-12-21'

SELECT * FROM #duplicate2

-- a solution

SELECT [firstName],

[Lastname],

AVG([grades]) AS [AVGgrade],

MAX([date]) AS [MostRecentGrade],

MIN([date]) AS [LeastRecentGrade]

FROM #duplicate2

GROUP BY [firstName], [Lastname]

Cheers

ChrisM

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

Nabha SSCrazy Eights Points: 8534 More actions · Answer 5

Not sure if I am missing something here but isnt it the same query that was posted earlier here

I guess discussion was already started there!

---------------------------------------------------------------------------------

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 6

Nabha (1/27/2010)
Not sure if I am missing something here but isnt it the same query that was posted earlier here
I guess discussion was already started there!

Guess the OP didn't like the awkward questions there, Nabha - like which aggregate function to use for date. Since the same questions have arisen in this thread, perhaps we can expect another to pop up somewhere.

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

Nabha SSCrazy Eights Points: 8534 More actions · Answer 7

Chris Morris-439714 (1/27/2010)
Guess the OP didn't like the awkward questions there, Nabha - like which aggregate function to use for date. Since the same questions have arisen in this thread, perhaps we can expect another to pop up somewhere.

🙂

---------------------------------------------------------------------------------

Nabha SSCrazy Eights Points: 8534 More actions · Answer 8

Will this do? 🙂

(borrowed the script from Chris)

SELECT *,

(SELECT AVG([grades])

FROM #duplicate2 T2

Where T2.FIRSTNAME = T1.FIRSTNAME

GROUP BY T2.FIRSTNAME ) AS [AVG]

FROM #Duplicate2 T1

---------------------------------------------------------------------------------