Adding two count(*) results together

Question

I have these two queries and I want to add them

SELECT count(*) FROM table2 GROUP BY table1_id
SELECT count(*) FROM table3 GROUP BY table1_id

I tried something similar to the accepted answer in this question. The difference is that I have a group by clause in both queries and I get the following error

ERROR: more than one row returned by a subquery used as an expression

********** Error **********

What am I missing?

This is a case where sample data and desired results would help convey what you are trying to do. — Gordon Linoff, Mar 13 '17 at 19:36
So several people are guessing at what you mean to do, and if one of them is right, great... but hopefully you see that there's a lack of clarity, so if none of these answers is hitting the mark, then here's what we need: show a small input sample and also show, given that input, what the desired output should be. — Mark Adelsberger, Mar 13 '17 at 19:38
(That said, as for what you're missing: when you put a subquery in the `SELECT` clause - as per the other answer you reference - it has to return just 1 result, and that result is inserted into each row of the outer query. Of course if the subquery is correlated it could be a different result for each row, but it can't be a whole result set coming back from one instance of the query.) — Mark Adelsberger, Mar 13 '17 at 19:40

score 4 · Accepted Answer · answered Mar 13 '17 at 19:33

You could join the two queries according to the table1_id:

SELECT a.table1_id, a.cnt + b.cnt
FROM   (SELECT   table1_id, COUNT(*) AS cnt
        FROM     table2 
        GROUP BY table1_id) a
JOIN   (SELECT   table1_id, COUNT(*) AS cnt
        FROM     table3 
        GROUP BY table1_id) b ON a.table1_id = b.table1_id

Note: This query implicitly assumes that both tables have the same table1_id values. If this is not the case, you'd need to use an outer join of some kind.

score 2 · Answer 2 · answered Mar 13 '17 at 19:32

2

Here is how to add the count of 2 tables:

WITH q1 AS (
    SELECT count(DISTINCT table1_id) as count from table2
), q2 AS (
    SELECT count(DISTINCT table1_id) as count from table3
)
SELECT (SUM(count + (SELECT count FROM q2)))
FROM q1

answered Mar 13 '17 at 19:32

Gab

3,404
1
11
22

score 0 · Answer 3 · answered Mar 13 '17 at 19:35

You have group by in your subquery, but don't show the groups. That seems suspicious.

You need to combine them. The first thought is FULL OUTER JOIN:

SELECT COALESCE(t2.table1_id, t3.table1_id) as table1_id,
       t2.cnt, t3.cnt,
       (COALESCE(t2.cnt, 0) + COALESCE(t3.cnt, 0) ) as t23_cnt
FROM (SELECT table1_id, count(*) as cnt FROM table2 GROUP BY table1_id
     ) t2 FULL OUTER JOIN
     (SELECT table1_id, count(*) as cnt FROM table3 GROUP BY table1_id
     ) t3
     ON t2.table1_id = t3.table1_id;

Adding two count(*) results together

3 Answers3