Results 1 to 4 of 4
  1. #1
    Join Date
    Oct 2008
    Location
    India
    Posts
    40

    Unanswered: Sum up the record counts of three tables

    There are three tables
    Current,History and archive.The contents of these tables are consolidated in one table called Full.

    I need to write a query in which i need to
    sum up the record count of Current,History and archive

    For example
    SELECT COUNT(*) AS C
    FROM CURRENT
    UNION
    SELECT COUNT(*) AS H
    FROM HISTORY
    UNION
    SELECT COUNT(*) AS A
    FROM ARCHIVE;

    Now i need to sum up the count values(c,h,a) in the same query.
    Can anyone help me on this?

  2. #2
    Join Date
    Apr 2008
    Location
    Iasi, Romania
    Posts
    561
    Provided Answers: 2
    SUM(ISNULL(C,0) + ISNULL(H,0) + ISNULL(A,0)) AS Total_SUM
    Florin Aparaschivei
    DB2 9.7, 10.5 on Windows
    Iasi, Romania

  3. #3
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    aflorin27, i don't think it is possible for a COUNT to return NULL, so ISNULL isn't needed

    here is an even simpler solution:
    Code:
    SELECT ( SELECT COUNT(*) FROM current )
         + ( SELECT COUNT(*) FROM history )
         + ( SELECT COUNT(*) FROM archive ) AS grand_total

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  4. #4
    Join Date
    Apr 2008
    Location
    Iasi, Romania
    Posts
    561
    Provided Answers: 2
    I miss the "in the same query" part of his post. I had the impression that he needs a SELECT for the results of his query
    Florin Aparaschivei
    DB2 9.7, 10.5 on Windows
    Iasi, Romania

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •