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

Question

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.)

Answer 1

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!

Answer 2

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
)

Answer 3

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.