SitePoint Sponsor

User Tag List

Results 1 to 3 of 3

Thread: distinct count

Hybrid View

  1. #1
    Get my greedy down dotJoon's Avatar
    Join Date
    Apr 2003
    Location
    daejeon, South Korea
    Posts
    2,223
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    distinct count

    Code:
    myTable1
    (id) name
    (1) Tom
    (2) Jane
    (3) Chris
    (4) Judy
    (5) Mary
    (6) Jack
    (7) James
    (8) Bill
    
    myTable
    (pom) kid
    (1)  2
    (3)  1
    (3)  4
    (5)  8
    Let's suppose I have 2 tables like the above.

    The code1 below produces the result1 below.
    Code:
    code1
    select name
    from myTable1
    left join myTable on myTable1.id=myTable.pom
    where
    myTable.pom is not null
    order by name
    
    result1
    Chris
    Chris
    Mary
    Tom
    I can count the number of records in result1 with the code below.
    Code:
    code2
    select count(*) as count
    from myTable1
    left join myTable on myTable1.id=myTable.pom
    where
    myTable.pom is not null
    
    result2
    4
    Since Chris comes 2 times in result1, I like the code3 and its result3 below.
    Code:
    select distinct name
    from myTable1
    left join myTable on myTable1.id=myTable.pom
    where
    myTable.pom is not null
    order by name
    
    result3
    Chris
    Mary
    Tom
    Now I have a problem. i.e, How can I count the number of records in result3?

    The following code4 is one of my trials for it. but it still says 4 instead of 3.
    Code:
    code4
    select distinct count(*) as count
    from myTable1
    left join myTable on myTable1.id=myTable.pom
    where
    myTable.pom is not null
    
    result4
    4

  2. #2
    SitePoint Mentor silver trophybronze trophy
    Mikl's Avatar
    Join Date
    Dec 2011
    Location
    Edinburgh, Scotland
    Posts
    1,608
    Mentioned
    66 Post(s)
    Tagged
    0 Thread(s)
    I don't know if this would work in MySQL, but in SQL Server I would do this:

    SELECT COUNT(DISTINCT Name) FROM ... etc.

    I think that's standard ANSI syntax, so it should work with most databases.

    Mike

  3. #3
    Get my greedy down dotJoon's Avatar
    Join Date
    Apr 2003
    Location
    daejeon, South Korea
    Posts
    2,223
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    Thank you, Mikl. It works fine in MySQL.


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
  •