Count occurrences of same element in two tables...

pearl2 · #1 (**permalink**) 03-31-04, 20:33

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.

Pat Phelan · #2 (**permalink**) 03-31-04, 21:27

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_id, name)

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_id, name)

SELECT              1, 'pete'

   UNION ALL SELECT 2, 'alice'

   UNION ALL SELECT 3, 'pete'

   UNION ALL SELECT 4, 'bob'

   UNION ALL SELECT 5, 'pete'



SELECT name, Count(*)

   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

pearl2 · #3 (**permalink**) 03-31-04, 21:34

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

Pat Phelan · #4 (**permalink**) 03-31-04, 21:36

Oh well, then just create the view and count it instead. No major strain or pain.

-PatP

pearl2 · #5 (**permalink**) 03-31-04, 21:41

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.

Pat Phelan · #6 (**permalink**) 04-01-04, 00:12

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

pearl2 · #7 (**permalink**) 04-01-04, 00:32

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

ajandris · #8 (**permalink**) 04-01-04, 04:28

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

pearl2 · #9 (**permalink**) 04-01-04, 06:21

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

ajandris · #10 (**permalink**) 04-01-04, 08:08

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

pearl2 · #11 (**permalink**) 04-01-04, 08:24

Quote:

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?

Pat Phelan · #12 (**permalink**) 04-01-04, 10:03

Quote:

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

pearl2 · #13 (**permalink**) 04-01-04, 10:44

Quote:

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?

Pat Phelan · #14 (**permalink**) 04-01-04, 12:14

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

pearl2 · #15 (**permalink**) 04-01-04, 12:41

Quote:

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

Internet Services

Web Development

Digital Marketing

Consumer Tech