Results 1 to 5 of 5
  1. #1
    Join Date
    Oct 2003
    Location
    Toronto
    Posts
    5

    Unanswered: list all duplicate names (query help)

    we have a table with duplicate name

    1. how to list all duplicate customer names(occurring more than once), including frequency of occurrence.

    2. find out how many customers have more than 2 account

    Thanks

  2. #2
    Join Date
    Sep 2003
    Posts
    522
    select customer, [number of accounts]=count(*)
    from customer_account_table
    group by customer
    having count(*) > 1

  3. #3
    Join Date
    Sep 2003
    Location
    New York, NY
    Posts
    136
    try this query

    select cust_name , count(cust_name) from customer_table group by cust_name order by count(cust_name) desc

  4. #4
    Join Date
    Jul 2002
    Location
    Village, MD
    Posts
    621
    Originally posted by ms_sql_dba
    select customer, [number of accounts]=count(*)
    from customer_account_table
    group by customer
    having count(*) > 1
    Above is answer to question #1. Below is answer to question#2.

    select count(*) from (
    select customer, [number of accounts]=count(*)
    from customer_account_table
    group by customer
    having count(*) > 1) as q

  5. #5
    Join Date
    Sep 2003
    Posts
    522
    actually, asnwer #1 lacks one line to become both #1 and #2:

    Code:
    select customer, [number of accounts]=count(*)
    from customer_account_table
    group by customer
    having count(*) > 1 
    compute count(customer)

Posting Permissions

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