Results 1 to 3 of 3
  1. #1
    Join Date
    Dec 2007
    Posts
    32

    Unanswered: count the number of records from multiple tables

    Hi
    I try to count the number of records from two tables.
    Table1 has
    id, AllowAccess
    11, Y
    11, Y
    11, Y
    22, Y
    22, Y

    Table2 has
    id, AllowAccess
    11, Y
    11, Y
    11, Y
    22, Y
    22, Y
    22, Y

    I try to join the 2 tables and get a count of record groups by id.
    i.e. I want to get the following result
    id, Table1Count, Table2Count
    11, 3 , 3
    22, 2 , 3

    I try the following query
    select a.id, count(a.AllowAccess), count(b.AllowAccess) from Table1 a, Table2 b
    where a.id = b.id
    and a.AllowAccess = 'Y'
    and b.AllowAccess = 'Y'
    group by a.id

    However, I get a very strange result:
    id, Table1Count, Table2Count
    11, 9 ,9
    22, 6 ,6

    Does anyone know what's wrong with my query?

    Thanks

  2. #2
    Join Date
    May 2005
    Location
    South Africa
    Posts
    1,365
    Provided Answers: 1
    Quote Originally Posted by tiger66
    However, I get a very strange result
    Not strange at all as your id is not unique
    The first id=11 in table1 join to 3 rows with id=11 in table2
    The second id=11 in table1 join to 3 rows with id=11 in table2
    The third id=11 in table1 join to 3 rows with id=11 in table2
    giving a total of 9 rows

    Code:
    select id, sum(c1), sum(c2) 
    from 
    (select id,count(*) c1, 0 c2 from t1 group by id
     union all 
     select id,0,count(*) from t2 group by id
    )v1
    group by id

  3. #3
    Join Date
    Dec 2007
    Posts
    32
    Thanks so much pdreyer. That's exactly what I needed

Posting Permissions

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