How do I add two count(*) results together on two different tables?
MysqlCountSumMysql Problem Overview
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.)
Mysql Solutions
Solution 1 - Mysql
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!
Solution 2 - Mysql
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;
Solution 3 - Mysql
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
)
Solution 4 - Mysql
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.
Solution 5 - Mysql
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
Solution 6 - Mysql
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
Solution 7 - Mysql
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
)
Solution 8 - Mysql
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