mysql sum 两张表_MySQL查询，COUNT和SUM（具有两个联接表）

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.

backlinks

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

domains

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: count = 2, total = 5.

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!

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.

解决方案

Thanks for the help. Sorry it was so hard to explain I need a MySQL fiddle :)

If anyones interested heres what I ened up with:

SELECT SUM(1) AS count, SUM(total) AS total

FROM

(

SELECT 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`

) AS result