SitePoint Sponsor

User Tag List

Results 1 to 7 of 7
  1. #1
    Afrika
    Join Date
    Jul 2004
    Location
    Nigeria
    Posts
    1,737
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Compare duplicate fields

    hello,
    how do i check to compare two columns to show duplicate fields. And how many times they appear

    And to list them in seperate rows
    thanks
    Afrika

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,350
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    could you give an example please
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    Afrika
    Join Date
    Jul 2004
    Location
    Nigeria
    Posts
    1,737
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    hi Rudy,
    am working on this example below, I sorted out the previous problems i was having.

    select entryregno, refcode from studentadmission
    where currentsession = '2004-2005'

    group by entryregno, refcode
    having count(entryregno) > 1
    I get no value

    if i use
    select entryregno from studentadmission
    where currentsession = '2004-2005'

    group by entryregno
    having count(entryregno) > 1
    I get 43 records.

    How do i compare columns as in the code above

    Afrika

  4. #4
    Afrika
    Join Date
    Jul 2004
    Location
    Nigeria
    Posts
    1,737
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Am trying this code but its giving me 86 records instead of 43, or am i missing something here ?

    select * from studentadmission
    where currentsession = '2004-2005'
    and EntryRegNo in(
    select EntryRegNo from studentadmission
    where currentsession = '2004-2005'
    group by EntryRegNo having count(*) > 1)

  5. #5
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,350
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    i'm sorry, i still don't understand what you want

    you get no results from the first query because each combination of entryregno and refcode is distinct

    there are 43 entryregno values with more than one refcode each

    could you show me a few sample rows of this data, and then please try to rephrase what you're asking
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  6. #6
    Afrika
    Join Date
    Jul 2004
    Location
    Nigeria
    Posts
    1,737
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Sorry if i didnt explain myself properly.

    We have a table studentadmission table, with about 12 columns of data
    But i am interested in two rows

    1.entryregno
    2.currentsession

    PROBLEM:
    1. I would like to get all duplicate records for the entryregno
    2. That has the currentsession column value as '2004-2005'

    thanks

    Am currently using the code below, but it shows 86 records

    select * from studentadmission
    where currentsession = '2004-2005'
    and EntryRegNo in(
    select EntryRegNo from studentadmission
    where currentsession = '2004-2005'
    group by EntryRegNo having count(*) > 1)


    However if i run 2tests with these queries i get a difference of 43 records.

    query1: select distinct(EntryRegNo) from studentadmission where currentsession = '2004-2005'
    query2: select EntryRegNo from studentadmission where currentsession = '2004-2005'
    difference between the two = 43 records

  7. #7
    Afrika
    Join Date
    Jul 2004
    Location
    Nigeria
    Posts
    1,737
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Oh, i think i have seen the problem.

    1.The first query selects distinct and gives me 43
    2. While the second query uses the in keyword and gets 86.

    I guess this settles the issue.

    Sorry if i didnt explain it better, but at times, when things arent just working well, ...
    ... the mindset isnt too clear.

    Have a nice day
    Afrika


Bookmarks

Posting Permissions

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