Results 1 to 15 of 15
  1. #1
    Join Date
    Nov 2003
    Location
    Sinapore
    Posts
    187

    Unanswered: Count occurrences of same element in two tables...

    Hi,

    I'm still trying to figure out how to count how many times an item has appeared in two tables. Below is the output of the two tables:
    Code:
    main table (m_id, name)
    1 pete
    2 jane
    3 pete
    4 pete
    5 alice
    6 joe
    7 mike
    8 pete
    
    child table (c_id, name)
    1 pete
    2 alice
    3 pete
    4 bob
    5 pete
    A query on the number of occurences of 'pete' should return '7' while a query on the number of occurences of 'bob' should return '1''.

    I suppose I could have two queries to do the counting but is there a way to do it with just one?

    I tried with the following LEFT JOIN and got a count of '12' for a query on 'pete':
    Code:
    SELECT COUNT(*) FROM main
       LEFT JOIN child USING (name)
       WHERE main.name='pete'
    It doesn't seem to be doing it right as I should be getting '7' and not '12'.

    Hope someone can enlighten me

    Thanks in anticipation.

  2. #2
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Version 4.1 of MySQL will support un-named views, so you could use:
    PHP Code:
    CREATE TABLE main (
       
    m_id        INT
    ,  name        VARCHAR(20))

    INSERT INTO main (m_idname)
    SELECT              1'pete'
       
    UNION ALL SELECT 2'jane'
       
    UNION ALL SELECT 3'pete'
       
    UNION ALL SELECT 4'pete'
       
    UNION ALL SELECT 5'alice'
       
    UNION ALL SELECT 6'joe'
       
    UNION ALL SELECT 7'mike'
       
    UNION ALL SELECT 8'pete'

    CREATE TABLE child (
       
    c_id        INT
    ,  name        VARCHAR(20))

    INSERT INTO child (c_idname)
    SELECT              1'pete'
       
    UNION ALL SELECT 2'alice'
       
    UNION ALL SELECT 3'pete'
       
    UNION ALL SELECT 4'bob'
       
    UNION ALL SELECT 5'pete'

    SELECT nameCount(*)
       
    FROM (SELECT name
          FROM main
          UNION ALL SELECT name
             FROM child
    ) AS a
       GROUP BY name

    DROP TABLE main
    DROP TABLE child 
    If you are running an earlier version of MySQL, you could create a view of the UNION and use it.

    -PatP

  3. #3
    Join Date
    Nov 2003
    Location
    Sinapore
    Posts
    187
    Thanks Pat!

    The msyql version I'm using is 4.0.16. I can upgrade it but the server in which a site of mine will be hosted is using mysql 4.0.13 (they are not likely to upgrade it so soon). So un-named views is not supported yet

  4. #4
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Oh well, then just create the view and count it instead. No major strain or pain.

    -PatP

  5. #5
    Join Date
    Nov 2003
    Location
    Sinapore
    Posts
    187
    I see. Creating views is something new to me. How do I do that? I've already created the two tables 'main' and 'child' and have already inserted the values into them.

  6. #6
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    I'm sorry, my mistake. MySQL doesn't support views yet either. I'm afraid that your only real answer is to use a scratch table then.

    -PatP

  7. #7
    Join Date
    Nov 2003
    Location
    Sinapore
    Posts
    187
    Thanks Pat!

    I wrote two SELECT COUNT(*) queries and add up the results of the two counts.

    Code:
    # perl
    # the perl code is incomplete
    
    SELECT COUNT(*)
      FROM main
      WHERE name=?
    
    my $count = 0;
    
    $count = $sth->fetchrow();
    
    SELECT COUNT(*)
       FROM child
       WHERE name=?
    
    $count += $sth->fechrow();
    
    # ? gets set to 'pete' when query executes
    So I gather that can't be done with a single query without using more advanced features?

    cheers
    Last edited by pearl2; 04-01-04 at 01:36.

  8. #8
    Join Date
    Mar 2004
    Posts
    8

    Angry

    Maximum what can be done in one select is:

    select count(0), name from t1 group by name
    union
    select count(0), name from t2 group by name

  9. #9
    Join Date
    Nov 2003
    Location
    Sinapore
    Posts
    187
    Thanks, ajandris!

    I don't seem to be getting the correct result...
    Code:
    # create main
    CREATE TABLE main (
    	m_id SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
    	user VARCHAR(20) NOT NULL,
    );
    
    # create child
    CREATE TABLE child (
    	c_id SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
    	user VARCHAR(20) NOT NULL,
    );
    
    # insert main
    INSERT INTO main VALUES(NULL, 'pete');
    INSERT INTO main VALUES(NULL, 'jane');
    INSERT INTO main VALUES(NULL, 'pete');
    INSERT INTO main VALUES(NULL, 'pete');
    INSERT INTO main VALUES(NULL, 'alice');
    INSERT INTO main VALUES(NULL, 'joe');
    INSERT INTO main VALUES(NULL, 'mike');
    INSERT INTO main VALUES(NULL, 'pete');
    
    # insert child
    INSERT INTO child VALUES(NULL, 'pete');
    INSERT INTO child VALUES(NULL, 'alice');
    INSERT INTO child VALUES(NULL, 'pete');
    INSERT INTO child VALUES(NULL, 'bob');
    INSERT INTO child VALUES(NULL, 'pete');
    
    # sql
    SELECT COUNT(*), user FROM main GROUP BY user
       UNION SELECT COUNT(*), user FROM child GROUP BY user
    
    # result
    count(*) user
    1           alice
    1           jane
    1           joe
    1           mike
    4           pete
    1           bob
    3           pete
    I'm puzzled by the result. Why is it '1' for alice and not '2' as there are two listings for 'alice'? Is there a way to achieve the following (on mysql 4.0.16)?
    Code:
    # result
    count(*) user
    7           pete
    2           alice
    1           bob
    1           jane
    1           joe
    1           mike
    That is, counting the 'user' columns in both tables and sorting the totals.

    Please help
    Last edited by pearl2; 04-01-04 at 09:28.

  10. #10
    Join Date
    Mar 2004
    Posts
    8

    Exclamation

    If main table contains all user names and child table consists of records existed in main table then solution will be simple, but I see from exaple, that there is NO relation main->child.
    It is the main problem. Reorganize data structure to main->child relation and your problem will be solved.

    result set contains 'alice' once because union shows unique records from both selects. Use UNION ALL, to have '1','alice' thice ...

    mySQL does not have syntax like Oracle SELECT * FROM (SELECT ...) ORDER BY ...

    if U need more assistance email me ajandris@parks.lv ... with high priority
    Last edited by ajandris; 04-01-04 at 09:15.

  11. #11
    Join Date
    Nov 2003
    Location
    Sinapore
    Posts
    187
    Originally posted by ajandris
    If main table contains all user names and child table consists of records existed in main table then solution will be simple, but I see from exaple, that there is NO relation main->child.
    It is the main problem. Reorganize data structure to main->child relation and your problem will be solved.

    result set contains 'alice' once because union shows unique records from both selects. Use UNION ALL, to have '1','alice' thice ...

    mySQL does not have syntax like Oracle SELECT * FROM (SELECT ...) ORDER BY ...

    if U need more assistance email me ajandris@parks.lv ... with high priority
    Cool and thanks a great deal

    With the modified sql, I got the entire result set as follows:
    Code:
    # sql
    SELECT COUNT(*), user FROM main GROUP BY user
       UNION ALL SELECT COUNT(*), user FROM child GROUP BY user
    
    # resutl
    count(*) user
    1           alice
    1           jane
    1           joe
    1           mike
    4           pete
    1           alice
    1           bob
    3           pete
    Now all I need is to transform that to:
    Code:
    # result
    count(*) user
    7           pete
    2           alice
    1           bob
    1           jane
    1           joe
    1           mike
    I suppose I could do it with with a little bit of perl code to total the result returned by sql and then sort the totals.

    Is it possible to do the totalling in the sql itself?
    Last edited by pearl2; 04-01-04 at 09:27.

  12. #12
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Originally posted by pearl2 So I gather that can't be done with a single query without using more advanced features?
    Not using MySQL 4.0.whatever. It will be possible, but we ain't dere yet!

    This is a wild thought, but you could still create a scratch table that contained all of the names from both Main and Child, then total the scratch table. A pain in the patoot (and I'd rather just use the Perl), but it is another option.

    -PatP

  13. #13
    Join Date
    Nov 2003
    Location
    Sinapore
    Posts
    187
    Originally posted by Pat Phelan
    Not using MySQL 4.0.whatever. It will be possible, but we ain't dere yet!

    This is a wild thought, but you could still create a scratch table that contained all of the names from both Main and Child, then total the scratch table. A pain in the patoot (and I'd rather just use the Perl), but it is another option.

    -PatP
    Thanks PatP

    I figured that the query would get pretty messy, as I need to pull out the top 10 names that occurred the most number of times. So I took the easy way out.

    Each time an entry is added to the main table, I update its count in a counter table. Similarly, each time an entry is added to the child table, the corresponding count in the count table is udpated.

    For example, given the following insertions...
    Code:
    # insert into main
    INSERT INTO main VALUES(NULL, 'pete');
    INSERT INTO main VALUES(NULL, 'jane');
    INSERT INTO main VALUES(NULL, 'pete');
    INSERT INTO main VALUES(NULL, 'pete');
    
    # insert into child child
    INSERT INTO child VALUES(NULL, 'pete');
    INSERT INTO child VALUES(NULL, 'alice');
    INSERT INTO child VALUES(NULL, 'pete');
    INSERT INTO child VALUES(NULL, 'bob');
    INSERT INTO child VALUES(NULL, 'jane');
    
    # table main
    id  user
    ---------
    1   pete
    2   jane
    3   pete
    4   pete
    
    #  table child
    id  user
    ---------
    1   pete
    2   alice
    3   pete
    4   bob
    5   jane
    
    # table counter
    user   count
    -------------
    pete   5
    jane   2
    alice   1
    bob    1
    So the totals are now in one table, which would make it easier to formulate a query to obtain the top 5 or 10.

    It's a bit of redundancy, as a third table is created just for the totals. Do you think it's a no-no solution?
    Last edited by pearl2; 04-01-04 at 13:43.

  14. #14
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    You have to find the solution that works the best for whoever is going to maintain it (you, in this case). I'd keep gentle pressure on the web host to get them to upgrade to MySQL 4.1 (or later) so you could use the simple SQL query, but for now your solution works nicely.

    -PatP

  15. #15
    Join Date
    Nov 2003
    Location
    Sinapore
    Posts
    187
    Originally posted by Pat Phelan
    You have to find the solution that works the best for whoever is going to maintain it (you, in this case). I'd keep gentle pressure on the web host to get them to upgrade to MySQL 4.1 (or later) so you could use the simple SQL query, but for now your solution works nicely.

    -PatP
    Thanks a zillion

Posting Permissions

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