how to group my results

  • 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

  • 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

  • here is sample data;

    1 Nicholas Edet 80 2009-12-22 00:00:00.000

    2 Nicholas Edet 95 2009-12-22 00:00:00.000

    3 Nicholas Edet 90 2009-12-22 00:00:00.000

    4 Emanuella Edet 76 2009-12-22 00:00:00.000

    5 Emanuella Edet 80 2009-12-22 00:00:00.000

    6 Emanuella Edet 97 2009-12-22 00:00:00.000

    7 Emanuella Edet 76 2009-12-20 00:00:00.000

    8 Nicholas Edet 95 2009-12-20 00:00:00.000

    9 jackson Devo 70 2009-12-21 00:00:00.000

    10 Sarah Palin 79 2009-12-21 00:00:00.000

    then i want the results to be like this with all the columns included;

    jackson Devo 70

    Emanuella Edet 82

    Nicholas Edet 90

    Sarah Palin 79

    any help is welcome and i appreciate it guys thank you

  • 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

  • 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

  • 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.

  • 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

  • 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

  • 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

    ssn countDuplicateSSN

    212671777 2

    213456123 2

    I want output to look like this:

    Barry Young 212671777

    Laban Meese 212671777

    Larry Bechold 213456123

    Barry Young 213456123

    -

  • 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

    ssn countDuplicateSSN

    212671777 2

    213456123 2

    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...)

  • Very nice. Thank you Revenant.

    -

  • 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?:

    Laban Meese 212671777

    Barry Young 213456123

    *edit,The table contains the following:

    empid fname lname mngrid ssn

    1 Larry Bechold 5 213456123

    2 Barry Young 5 213456123

    3 Rob Sinclaire 4 212671777

    4 Laban Meese NULL 212671777

    5 Sarge Lutvok NULL 523656667

    -

  • 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

  • Hi Drew,

    That query generates this:

    Msg 156, Level 15, State 1, Line 9

    Incorrect syntax near the keyword 'RowCount'.

    -

  • 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