Need help for counting data

Hello everyone, I'm not that good with SQL, but have a basic understanding. This is my request:

I have 1 DB with 2 tables, List1 & List2, each with 25 emails.


EXAMPLE

list1:

user@aol.com
user2@aol.com
user3@aol.com
user1@hotmail.com
user2@hotmail.com
user3@hotmail.com
user4@hotmail.com
user@yahoo.com


list2:

user3@aol.com
user2@aol.com
user4@hotmail.com
user@yahoo.com


What I want to do is to make a union and output this:

+-----------------------------------------------+
|Domain |List1 | List2 | 1-2 | % Opened |
+-----------------------------------------------+
|aol.com | 3 | 2 | 1 | 66% |
|hotmail.com | 4 | 1 | 3 | 25% |
|yahoo.com | 1 | 1 | 0 | 100% |
+-----------------------------------------------+

+ the domain column will be domains encounter
+ the List1 column will be total number of domains encounter for each domain, example found 3 for aol.com
+ the List2 column will be number of domains encounter in list 2, for example: found 2 for aol.com in list 2
+ the colums 1-2 will be the substraction, this I can handle it
+ the last column will be the percentage and also can handle it.


I can't understand how I can do the 3 first steps. Can someone help me? Is this possible? Thank you, and look forward to this

It won't be too easy to do what you want to do. The problem is that you don't know what URL's are in the database. For example, to count how many 'aol.com' addresses there are, you would use something like:

select count(*) from list1 where link LIKE '%@aol.com';

Unfortunately, you don't know what sites have been clicked on. You can't do a 'select distinct' because the links aren't distinct because of the initial part of the email addresses.

I don't know if there are string-manipulation functions in MYSQL that could trim only the domain name so you could use simply count them on-the-fly. If they are such functions then you can do it without too much trouble, because you could select distinct domains from the tables, then use these domains in the 'like' part to count them in each table.

Another way is to seperate the user part from the domain name before you insert the data, where you store the domain names in their own table and the other table has a foreign key to the domain. This is probably a better idea, because you can group by the foreign key when counting the number in each, which is very easy. I would do it this way, as it makes things much easier in the end.

I am sorry if this is too little detail, if it is please ask more about it.

You could use the SUBSTRING_INDEX() function to seperate the email addresses. Have a look at the MySQL manual for proper usage.