Results 1 to 1 of 1

Thread: Query doubt

  1. #1
    Join Date
    Jul 2010

    Unanswered: Query doubt


    The table design is as follows

    CREATE TABLE [dbo].[test](
    [name] [nvarchar](50) NULL,
    [number] [nvarchar](40) NULL,
    [amount] [decimal](20, 2) NULL
    ) ON [PRIMARY]


    insert into test values('john','2A','342')
    insert into test values('robert','2A','342')
    insert into test values('cindy','2A','342')
    insert into test values('missy','2A','342')
    insert into test values ('jackie','3C','206')
    insert into test values('donna','3c','206')
    insert into test values('jamie','3d','205')
    insert into test values('jay','3d','124')
    insert into test values ('eric','2A','342')

    The Query I used
    select name=max(name),amount,number,count(*) as duplicates
    from test
    group by number,amount
    having COUNT(number)>1 and MIN(name)<>MAX(name)
    order by COUNT(*) desc

    Query output:
    name amount number duplicates
    robert 342.00 2A 5
    jackie 206.00 3C 2

    Please let me know how can I list all the different names who have same number and amount and the number of duplicates and not just the maximum name.

    Could you please let me know a way to display the output in the following way:

    I am not sure if this can be done through SQl

    name name name name name number amount duplicates
    john robert cindy missy eric 2A 342 5
    jackie donna null null null 3c 206 2

    Thank you!!
    Last edited by tammy2512; 09-26-10 at 00:30.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts