MySQL query, COUNT and SUM with two joined tables

[admin]

I need a little help with a MySQL query.

I have two tables one table is a list of

backlinks

with a

is_homepage

(bool) flag. The second table is a list of the

domains

for all of the

backlinks

, a was

link_found

(bool) flag, and a

url_count

column which is the number of rows in the

backlinks

table that are associated with each domain.

Note that the

domain_id

column is the foreign key to the domain table

id

column. Heres some sample data.

<strong>backlinks</strong>

id    domain_id    is_homepage    page_href
1     1            1              http://ablog.wordpress.com/
2     1            0              http://ablog.wordpress.com/contact/
3     1            0              http://ablog.wordpress.com/archives/
4     2            1              http://www.somewhere.org/
5     2            0              http://www.somewhere.org/page=3
6     3            1              http://www.great-fun-site.com/
7     3            0              http://www.great-fun-site.com/index.html
8     4            0              http://red.blgspot.com/page=7
9     4            0              http://blue.blgspot.com/page=9

<strong>domains</strong>

id    url_count    link_found    domain_name
1     3            1             wordpress.com
2     2            0             somewhere.org
3     2            1             great-fun-site.com
4     2            1             blgspot.com

The results Im looking to get from the above data would be: <strong>count = 2, total = 5</strong>.

Im trying to get the count of rows from the domains table (count) and then the sum of the

url_count

(total) from the domains table WHERE

link_found

is 1 and where one of the links in the backlink table

is_homepage

is 1.

Here's the query I'm trying to work with.

SELECT SUM(1) AS count, SUM(`url_count`) total
FROM `domains` AS domain 
LEFT JOIN `backlinks` AS link ON link.domain_id = domain.id 
WHERE domain.id IN (
        SELECT DISTINCT(bl.domain_id)
        FROM `backlinks` AS bl
        WHERE bl.tablekey_id = 11
        AND bl.is_homepage = 1
)
AND domain.link_found = 1
AND link.is_homepage = 1
GROUP BY `domain`.`id`

The problem with this query is that it returns a row for each entry in the

domains

table. I think I might need one more sub query to add up the returned results but I'm not sure if that's correct. Does anyone see what I'm doing wrong? Thank you!

<hr>

EDIT:

The problem I'm having is that if there are more than one homepage in the back-links table then its counted multiple times. I need to only count each domain once.