How do I add two count(*) results together on two different tables?

I have two tables: Toys and Games.

+--------------------+------------------+
| Field              | Type             |
+--------------------+------------------+
| toy_id             | int(10) unsigned |
| little_kid_id      | int(10) unsigned |
+--------------------+------------------+


+--------------------+------------------+
| Field              | Type             |
+--------------------+------------------+
| game_id            | int(10) unsigned |
| little_kid1        | int(10) unsigned |
| little_kid2        | int(10) unsigned |
| little_kid3        | int(10) unsigned |
+--------------------+------------------+

A little kid can have multiple toys. A little kid can be participating in multiple games at once.

I want a query that will give me the total number of toys + games that a little_kid is involved with.

Basically, I want the sum of these two queries:

SELECT COUNT(*) FROM Toys WHERE little_kid_id = 900;
SELECT COUNT(*) from Games WHERE little_kid1 = 900
OR little_kid2 = 900
OR little_kid3 = 900;

Is it possible to get this in a single SQL query? Obviously, I can sum them programmatically, but that's less desirable.

(I realize that the contrived example makes the schema look ineffecient. Let's assume that we can't change the schema.)

115529 次浏览
小开
最佳答案

Wrap them up and use subqueries:

SELECT
(SELECT COUNT(*) FROM Toys WHERE little_kid_id = 900)+
(SELECT COUNT(*) from Games WHERE little_kid1 = 900
OR little_kid2 = 900
OR little_kid3 = 900)
AS SumCount

Voila!

小开 
SELECT COUNT(1) FROM
(
SELECT 1 FROM Toys WHERE little_kid_id = 900
UNION
SELECT 1 FROM Games WHERE little_kid1 = 900
OR little_kid2 = 900
OR little_kid3 = 900
)
小开

Depending on how much this query is likely to be run and how often the data changes you could periodically put data into an aggregated table like this:

CREATE TABLE aggregated (
little_kid_id INT UNSIGNED,
games_count INT UNSIGNED,
toys_count INT UNSIGNED,
PRIMARY KEY (little_kid_id)
);

Performance wise that would be s**t hot fast and avoids any nasty sub-queries.

小开

Try this one...

db: mysql


SELECT  SUM(dum.tab) AS total FROM (
SELECT COUNT(b.category_id) AS tab FROM tblcategory AS b WHERE b.category_id=1
UNION  ALL
SELECT COUNT(a.category_id) AS tab FROM tblcategory AS a WHERE a.category_id=2
) AS dum
小开 
  SELECT  M.*,M.TOYSCOUNT+M.GAMECOUNT
FROM (
(SELECT COUNT(*) FROM Toys WHERE little_kid_id) AS TOYSCOUNT,
(SELECT COUNT(*) from Games WHERE little_kid1 = 900 OR little_kid2 = 900 OR little_kid3 = 900) AS GAMECOUNT
) M
小开 
SELECT
((SELECT COUNT(*) FROM Toys WHERE little_kid_id = 900)+
(SELECT COUNT(*) from Games WHERE little_kid1 = 900
OR little_kid2 = 900
OR little_kid3 = 900))
AS Sum FROM DUAL;

Selecting from the DUAL Table

小开 
select t1.tx,t2.px,t3.mx,t2.px + t3.mx
as total from(
SELECT COUNT (DISTINCT id) as tx
FROM Customer) as t1
cross join(
select COUNT (DISTINCT name) as px
FROM details
) as t2
cross join(
select count (distinct device_id) as mx
from detailconfig
) as t3
小开 
SELECT COUNT(1) FROM
(
(SELECT 1 FROM Toys WHERE little_kid_id = 900
UNION
SELECT 1 FROM Games WHERE little_kid1 = 900
OR little_kid2 = 900
OR little_kid3 = 900) as temptable
)

提交答案