Post reply

Count From two tables

  • August 6, 2002 at 2:54 pm

    #79553

    Here is the deal:

    I need to Count(*) From two different tables and have it end up in one column. There are many stored procs that return the column that is used in many calculations...But we have been counting out of one table only.

    Does that make sence?

  • August 6, 2002 at 5:05 pm

    #434160

    Hi,

    Here is my response based on what I understood :

    There are tables Table1 and Table2 and you want to count the number of rows in Table1 and Table2 and store the result(I assume this is the sum)into a variable.....

    Select Sum(FinalCount) from

    (

    (Select Count(*) FinalCount from Table1)

    UNION

    (Select Count(*) FinalCount from Table2)

    )AS ResultTable

    I hope this results in what you want...

  • August 6, 2002 at 5:15 pm

    #434161

    Yes...I did do something like that...

    With a Full Outer Join...

    Is that the same as a UNION?

    Thanks.

  • August 6, 2002 at 6:01 pm

    #434165

    Make sure you use UNION ALL instead of just UNION. On the off-hand chance the counts for both tables come back the same, only one row will be returned. UNION without the ALL tosses out duplicates.

    This shows the difference:

    SELECT 3

    UNION

    SELECT 3

    vs.

    SELECT 3

    UNION ALL

    SELECT 3

    K. Brian Kelley

    bkelley@sqlservercentral.com

    http://www.sqlservercentral.com/columnists/bkelley/

    Edited by - bkelley on 08/06/2002 6:01:38 PM

    K. Brian Kelley
    @kbriankelley

  • August 7, 2002 at 2:23 am

    #434180

    Thanks a lot for the info on UNION and UNION ALL...

    I was using only UNION in a couple of places and I am now using UNION ALL...before the SPs went to the "unidentifiable error occuring" stage... 🙂

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply