Post reply

total rows

  • April 7, 2006 at 5:00 am

    #190077

    How to get total count of rows from 2 tables..in a single sql statement

  • April 7, 2006 at 5:24 am

    #631453

    Here's one way....

    select sum(c) as totalCount

    from  (

            select count(*) as c from table1

            union all 

            select count(*) as c from table2

          ) x

    /Kenneth

  • April 7, 2006 at 9:57 am

    #631541

    thks kenneth..could yu explain this a bit...is this a subquery? what is x at the end of query after )? Basically,i am just clueless..

    regards

    tal

  • April 7, 2006 at 10:02 am

    #631542

    How abt this....

    select (select count(*) from TableA )+(select count(*) from TableB ) as total

  • April 10, 2006 at 1:00 am

    #631661

    I'm sorry, it should have read like this to be (hopefully) a bit clearer..

    select sum(x.c) as totalCount

    from  (

            select count(*) as c from table1

            union all

            select count(*) as c from table2

          ) x

    The thing in between the parenthesises is a virtual table, and the 'x' is the name of the vitual table. You can view it like a 'temptable-on-the-fly' sort of.

    /Kenneth

  • April 10, 2006 at 6:21 am

    #631689

    thks kenneth..for clarification..thats great!!..i had no idea of virtual tables..any link on net where i can find more info?

    thks

  • April 10, 2006 at 8:12 am

    #631751

    They also go by the name 'derived tables'.

    You can find a good explanation here http://www.sqlservercentral.com/columnists/rmarda/derivedtablebasics_printversion.asp

    /Kenneth

Viewing 7 posts - 1 through 6 (of 6 total)

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