CODEWITHSUNDEEP
sqlpostgresqljoincountcommon-table-expression
Eleeist
Eleeist

Reputation: 7041

Counting from two tables according to single criteria

I am trying to do something like this:

SELECT COUNT(topic.topic_id) + COUNT(post.post_id)
FROM topic, post WHERE author_id = ?

Both tables have column author_id.

I get column reference "author_id" is ambiguous error.

How can I tell it that author_id is present in both tables?

Upvotes: 1

Views: 141

Answers (1)

Erwin Brandstetter
Erwin Brandstetter

Reputation: 657972

While you could, you most probably do not want to join both tables, since that might result in different counts. Explanation in this related answer:
Two SQL LEFT JOINS produce incorrect result

Two subqueries would be fastest:

SELECT (SELECT COUNT(topic_id) FROM topic WHERE author_id = ?)
     + (SELECT COUNT(post_id)  FROM post  WHERE author_id = ?) AS result

If topic_id and post_id are defined NOT NULL in their respective tables, you can slightly simplify:

SELECT (SELECT COUNT(*) FROM topic WHERE author_id = ?)
     + (SELECT COUNT(*) FROM post  WHERE author_id = ?) AS result

If at least one of both author_id columns is unique, a JOIN would work, too, in this case (but slower, and I wouldn't use it):

SELECT COUNT(t.topic_id) + COUNT(p.post_id) AS result
FROM   topic t
LEFT   post  p USING (author_id)
WHERE  t.author_id = ?;

If you want to enter the value only once, use a CTE:

WITH x AS (SELECT ? AS author_id)  -- enter value here
SELECT (SELECT COUNT(*) FROM topic JOIN x USING (author_id))
     + (SELECT COUNT(*) FROM post  JOIN x USING (author_id)) AS result

But be sure to understand how joins work. Read the chapter about Joined Tables in the manual.

Upvotes: 4

Related Questions