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

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

score 181 · Accepted Answer · answered May 05 '09 at 18:50

181

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!

answered May 05 '09 at 18:50

Eric

92,005
12
114
115

how can we get it without sub-query – Greesh Kumar Nov 08 '16 at 11:18
1

This won't work in Oracle without the FROM keyword. Apparently MySQL allows it! – Sampath Mar 11 '21 at 11:22
1

Use following on Oracle: `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 FROM DUAL`; – Sampath Apr 21 '21 at 05:57

score 12 · Answer 2 · edited Aug 30 '18 at 21:48

12

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

edited Aug 30 '18 at 21:48

adprocas

1,863
1
14
31

answered Feb 16 '18 at 10:15

anish

147
1
3

2

Provide description to your answer please. – MKR Feb 16 '18 at 13:19
Note: Selecting from the `dual` table is required for Oracle, but not MySQL. Therefore, for this specific question, [this answer](https://stackoverflow.com/a/826375/6340496) is more accurate. – S3DEV Sep 07 '20 at 08:46
1

Isn't this the same as the accepted answer? It's just wrapped in extra parentheses – Sebastián Palma Mar 19 '21 at 11:22

score 7 · Answer 3 · answered May 05 '09 at 18:52

7

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
)

answered May 05 '09 at 18:52

Adam Robinson

182,639
35
285
343

4

Some explanation would help for better understanding. – cezar Jan 24 '19 at 11:37

score 5 · Answer 4 · answered May 05 '09 at 23:06

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.

score 3 · Answer 5 · edited Nov 08 '12 at 09:58

3

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

edited Nov 08 '12 at 09:58

Gunther Struyf

11,158
2
34
58

answered Nov 08 '12 at 09:33

Scott Ramey

39
1

score 0 · Answer 6 · answered Dec 30 '16 at 10:55

0

  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

answered Dec 30 '16 at 10:55

Hitesh Makwana

1

score 0 · Answer 7 · answered May 04 '20 at 18:35

0

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
)

answered May 04 '20 at 18:35

PhantomReference

718
2
14
27

score -1 · Answer 8 · edited Apr 12 '19 at 04:25

-1

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

edited Apr 12 '19 at 04:25

Pika Supports Ukraine

3,612
10
26
42

answered Apr 12 '19 at 04:03

Arul

1
1

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

8 Answers8

Linked