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

mysql count sum
Runcible picture Runcible · May 5, 2009 · Viewed 87.7k times · Source

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

Eric picture Eric · May 5, 2009

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!

Related questions

select count(*) from table of mysql in php

I am able to get both the value and row of the mysql query result. But I am struggling to get the single output of a query. e.g.: $result = mysql_query("SELECT COUNT(*) FROM Students;"); I need the result …

Read More
How to get multiple counts with one SQL query?

I am wondering how to write this query. I know this actual syntax is bogus, but it will help you understand what I am wanting. I need it in this format, because it is part of a much bigger query. …

Read More
MySQL - Using COUNT(*) in the WHERE clause

I am trying to accomplish the following in MySQL (see pseudo code) SELECT DISTINCT gid FROM `gd` WHERE COUNT(*) > 10 ORDER BY lastupdated DESC Is there a way to do this without using a (SELECT...) in the WHERE clause because …

Read More