CODEWITHSUNDEEP
sqlsql-servert-sql
Karim Ali
Karim Ali

Reputation: 17

Count total rows from multiple queries?

How do I tie this to be one query and get the total count from all 3 queries? Thanks

SELECT COUNT(*)
FROM TopicArticleMappings a
INNER JOIN ArticleAuthor b
ON A.ArticleId = B.ArticleGuid
WHERE b.AuthorGuid = '9AC4AB6E-7454-4660-97CD-93D5BC6772C3'


SELECT COUNT(*) FROM JurisdictionAuthors a
INNER JOIN Authors b
ON a.AuthorGuid = b.AuthorGuid
WHERE b.AuthorGuid = '9AC4AB6E-7454-4660-97CD-93D5BC6772C3'

SELECT COUNT(*) FROM TopicConsultingEditors a
INNER JOIN tAuthors b
ON a.AuthorRef = b.AuthorRef
WHERE b.AuthorGuid = '9AC4AB6E-7454-4660-97CD-93D5BC6772C3'

Upvotes: 0

Views: 69

Answers (3)

Eray Balkanli
Eray Balkanli

Reputation: 7960

I would use variables instead of "union", like:

declare @JurisdictionAuthors int, @TopicConsultingEditors int, @TopicArticleMappings  int

SELECT @TopicArticleMappings = COUNT(*)
FROM TopicArticleMappings a
INNER JOIN ArticleAuthor b
ON A.ArticleId = B.ArticleGuid
WHERE b.AuthorGuid = '9AC4AB6E-7454-4660-97CD-93D5BC6772C3'

SELECT @JurisdictionAuthors = COUNT(*) 
FROM JurisdictionAuthors a
INNER JOIN Authors b
ON a.AuthorGuid = b.AuthorGuid
WHERE b.AuthorGuid = '9AC4AB6E-7454-4660-97CD-93D5BC6772C3'

SELECT @TopicConsultingEditors = COUNT(*) 
FROM TopicConsultingEditors a
INNER JOIN tAuthors b
ON a.AuthorRef = b.AuthorRef
WHERE b.AuthorGuid = '9AC4AB6E-7454-4660-97CD-93D5BC6772C3'

select @JurisdictionAuthors + @TopicConsultingEditors + @TopicArticleMappings

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269563

One method is to simply put SELECT in front and use subqueries:

SELECT (SELECT COUNT(*)
        FROM TopicArticleMappings a JOIN
             ArticleAuthor b
             ON A.ArticleId = B.ArticleGuid
        WHERE b.AuthorGuid = '9AC4AB6E-7454-4660-97CD-93D5BC6772C3'
       ) +
       (SELECT COUNT(*)
        FROM JurisdictionAuthors a JOIN
             Authors b
             ON a.AuthorGuid = b.AuthorGuid
        WHERE b.AuthorGuid = '9AC4AB6E-7454-4660-97CD-93D5BC6772C3'
       ) +
       (SELECT COUNT(*)
        FROM TopicConsultingEditors a JOIn
             tAuthors b
             ON a.AuthorRef = b.AuthorRef
        WHERE b.AuthorGuid = '9AC4AB6E-7454-4660-97CD-93D5BC6772C3'
       )

Upvotes: 1

Yogesh Sharma
Yogesh Sharma

Reputation: 50163

Use subquery with union all to combine all result-set :

SELECT SUM(CNT) AS Total_count
FROM (SELECT COUNT(*) AS CNT
      FROM . . .
      UNION ALL
      SELECT . . .
      FROM . . .
      UNION ALL
      SELECT . . . 
      FROM . . .
     ) t;

Upvotes: 1

Related Questions