Results 1 to 7 of 7
  1. #1
    Join Date
    Jan 2009
    Posts
    3

    Unanswered: Distinct Count NIGHTMARE!

    Hi

    I have seen that there are various posts regarding this topic, but unfortunately, none of them have answered my question.

    I can get a distinct count to work on one table and even two or more tables when only one column from a table is required, eg

    select count(a03)
    from TableA INNER JOIN (select distinct(a03) from TableB) as TableB on TableA.l03 = TableB.a03);

    However, it doesn't seem to work when I want more than one column returned from the table

    select count(a03)
    from TableA INNER JOIN (select distinct(a03), a05 from TableB) as TableB on TableA.l03 = TableB.a03);

    I have tried using two TableB tables but that doesn't work - I still get all the duplicate rows. Any ideas on how to solve this would be sooooo helpful. I am losing the will to live with this. So wish we used lovely Oracle

    Many thanks

    Alcy

  2. #2
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Quote Originally Posted by alcy
    So wish we used lovely Oracle
    Unless you know some Oracle syntax I don't, Oracle wouldn't help you.

    DISTINCT is not a function, but you are using it as one.

    Assuming you want to know how many distinct combinations of a03 and a05 there are then the SQL is something like:
    Code:
    select count(*)
    from TableA 
    INNER JOIN 
    (select distinct a03, a05 from TableB) as TableB 
    on TableA.l03 = TableB.a03;
    BTW - what is in TableA? Why bother joining to it?
    Testimonial:
    pootle flump
    ur codings are working excelent.

  3. #3
    Join Date
    Jan 2009
    Posts
    3
    Thanks for your reply. I have tried to count(*) method before to no avail. I acutally need to join quite a few tables together eventually, but am trying to break it down so that I can see where it is needed. TableA will be needed once I get this distinct query working.

    In Oracle a simple count(distinct a03) would work, without the need for subqueries.

  4. #4
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    You can use that syntax in Access too however you need to set the ANSI compatibility level. If you have lots of other queries, then this will not be an option as it can break other stuff.

    However, that would be invalid in your query anyway.

    Please can you confirm or correct this description of what you want to do:
    You want to get a count of distinct values for a03 used for each value of a05 in tableB, and join this result to some other tables in your database.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  5. #5
    Join Date
    Jan 2009
    Posts
    3
    Maybe I'm approaching this from the wrong angle.

    What I have is a list of Students L03 who may have 1 or more courses A05, but I only want to do a count of the distinct number of students. I will need field A05 to link to another table that I will require later on. I ultimately want a distinct count of the students without being concerned how many courses they are on.

    Hope this makes sense. I'm confusing myself as I'm typing!

  6. #6
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    In that case you have the correct count already in query 1, correct? Then you just need to add other joins to your require tables and leave that part exactly as it is.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  7. #7
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    You cannot use the DISTINCT predicate in this way. DISTINCT means that everyting you select must be different. If you have more than one column in your SELECT clause it probably means that every row is different unless two or more rows contain identical data.

    ex.
    ID Name Class
    Row1: 1 Mike English1
    Row2: 2 John Math2
    Row3: 3 John Sciences1

    SELECT DISTINCT Name, Class FROM Table1 WHERE (((Name) = "john"));

    is the same as

    SELECT Name, Class FROM Table1 WHERE (((Name) = "john"));


    Both will return Name and Class from Row2 and Row3 because
    "John Math2" is different from "John Sciences1"

    You have to create a first query (the DISTINCT one) that selects only one row with the DISTINCT predicate, then join this query to another one that returns the values you are looking for.

    Have a nice day!

Posting Permissions

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