January 26, 2010 at 10:00 pm
here is the structure of my table
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]
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, the problem is that 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
January 26, 2010 at 10:10 pm
Please provide sample data and what the desired output should look like. This will facilitate the process in helping you get a correct answer.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
January 26, 2010 at 10:17 pm
here is sample data;
1Nicholas Edet802009-12-22 00:00:00.000
2Nicholas Edet952009-12-22 00:00:00.000
3Nicholas Edet902009-12-22 00:00:00.000
4Emanuella Edet762009-12-22 00:00:00.000
5Emanuella Edet802009-12-22 00:00:00.000
6Emanuella Edet972009-12-22 00:00:00.000
7Emanuella Edet762009-12-20 00:00:00.000
8Nicholas Edet952009-12-20 00:00:00.000
9jackson Devo702009-12-21 00:00:00.000
10Sarah Palin792009-12-21 00:00:00.000
then i want the results to be like this with all the columns included;
jackson Devo70
Emanuella Edet82
Nicholas Edet90
Sarah Palin79
any help is welcome and i appreciate it guys thank you
January 26, 2010 at 10:33 pm
And how would you like the date column handled? The date column does not really go well with your proposed output and requirements. Which date should one use to correlate to the average grade?
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
January 26, 2010 at 10:40 pm
it doesnt matter as long as the result includes all the columns, you can exclude the id column because i dont know if its possible to include that in the result set
January 26, 2010 at 10:43 pm
Thats what Jason is asking.
Do you want the date column in the result set? If yes, what do you want the output to look like?
If you exclude id and date, then it looks like the SQL you have should work.
January 27, 2010 at 12:10 am
vstitte (1/26/2010)
Thats what Jason is asking.Do you want the date column in the result set? If yes, what do you want the output to look like?
If you exclude id and date, then it looks like the SQL you have should work.
Precisely. If you try to include the date column then you must also add it to the group by clause which will change your result set. However, if any date will work - then there are options available.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
January 27, 2010 at 5:24 am
I have tried this & it works well.....
when firstname,lastname and date are same then
definately it groups the grades and give the avearage result.
select firstname, lastname, date,avg(grades)
from duplicate2
group by firstname, lastname,date
January 19, 2012 at 8:16 pm
I have the same *general* problem with a slight twist. In KlineandKing's scenario the name needed to be listed only once for the sum(grade).
In my case there are duplicate ssns in my table, and the fnames and lnames for each occurence of ssn are different and I need to have them listed in the output next to each ssn that has been identified as a duplicate. Please help.
---create table
CREATE TABLE [dbo].[employee](
[empid] [int] IDENTITY(1,1) NOT NULL,
[fname] [varchar](30) NULL,
[lname] [varchar](30) NULL,
[mngrid] [int] NULL,
[ssn] [int] NOT NULL)
---insert data
insert into employee
(fname, lname, mngrid, ssn)
values
('Larry', 'Bechold', 5, 213456123),
('Barry', 'Young', 5, 213456123),
('Rob', 'Sinclaire', 4, 212671777),
('Laban', 'Meese', NULL, 212671777),
('Sarge', 'Lutvok', NULL, 523656667)
---retrieve only rows containing duplicate ssns
select ssn, count(ssn) as countDuplicateSSN
from employee
group by ssn
having COUNT(*)>1;
---results
ssncountDuplicateSSN
2126717772
2134561232
I want output to look like this:
Barry Young 212671777
Laban Meese 212671777
Larry Bechold 213456123
Barry Young 213456123
-
January 19, 2012 at 8:53 pm
aitchkcandoo (1/19/2012)
I have the same *general* problem with a slight twist. In KlineandKing's scenario the name needed to be listed only once for the sum(grade).In my case there are duplicate ssns in my table, and the fnames and lnames for each occurence of ssn are different and I need to have them listed in the output next to each ssn that has been identified as a duplicate. Please help.
---create table
CREATE TABLE [dbo].[employee](
[empid] [int] IDENTITY(1,1) NOT NULL,
[fname] [varchar](30) NULL,
[lname] [varchar](30) NULL,
[mngrid] [int] NULL,
[ssn] [int] NOT NULL)
---insert data
insert into employee
(fname, lname, mngrid, ssn)
values
('Larry', 'Bechold', 5, 213456123),
('Barry', 'Young', 5, 213456123),
('Rob', 'Sinclaire', 4, 212671777),
('Laban', 'Meese', NULL, 212671777),
('Sarge', 'Lutvok', NULL, 523656667)
---retrieve only rows containing duplicate ssns
select ssn, count(ssn) as countDuplicateSSN
from employee
group by ssn
having COUNT(*)>1;
---results
ssncountDuplicateSSN
2126717772
2134561232
I want output to look like this:
Barry Young 212671777
Laban Meese 212671777
Larry Bechold 213456123
Barry Young 213456123
Something like this --
select fname
,lname
,ssn
from ( select fname
,lname
,ssn
,ROW_NUMBER() over (
partition by ssn
order by ssn
) rowNumber
from employee ) a
where a.rowNumber > 1
(I followed you naming convention...)
January 19, 2012 at 10:31 pm
Very nice. Thank you Revenant.
-
January 19, 2012 at 11:42 pm
wait a minute, Revenant, that query is not quite returning the correct result. It returns only one name per ssn and yet there are two names sharing each of the duplicated ssns. Any other ideas?:
LabanMeese212671777
BarryYoung213456123
*edit,The table contains the following:
empidfnamelnamemngridssn
1LarryBechold5213456123
2BarryYoung5213456123
3RobSinclaire4212671777
4LabanMeeseNULL212671777
5SargeLutvokNULL523656667
-
January 20, 2012 at 7:50 am
This should work.
select fname
,lname
,ssn
from ( select fname
,lname
,ssn
,COUNT(*) over (
partition by ssn
) RowCount
from employee ) a
where a.RowCount > 1
If you have a new question, it's probably best to create a new thread. If it's related to an old thread, then you can always include a link to the old thread.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
January 20, 2012 at 11:13 am
Hi Drew,
That query generates this:
Msg 156, Level 15, State 1, Line 9
Incorrect syntax near the keyword 'RowCount'.
-
January 20, 2012 at 12:24 pm
aitchkcandoo (1/20/2012)
Hi Drew,That query generates this:
Msg 156, Level 15, State 1, Line 9
Incorrect syntax near the keyword 'RowCount'.
Just rename RowCount to say rc.
Viewing 15 posts - 1 through 15 (of 23 total)
You must be logged in to reply to this topic. Login to reply