Post reply

TSQL

  • November 20, 2007 at 1:36 am

    #200636

    Hi,

    Could any 1 help out on this.

    I need an sproc to find the total number of records of various tables which satisfies certain condition.

    For example:

    Consider 4 tables such as customer,booking,invoice,commission. I need to find total records of each table using single query that matches certain condition.

    Thanks in advance,

    Anitha

  • November 20, 2007 at 2:27 am

    #752553

    select

    (select count(*) customer where )

    (select count(*) booking where )

    (select count(*) invoice where )

    (select count(*) commission where )

  • November 20, 2007 at 5:15 am

    #752606

    select o.name, i.rows

    from sysobjects o, sysindexes i

    where i.id = o.id

    and i.indid < 2 and o.xtype = 'u'

  • November 20, 2007 at 5:26 am

    #752609

    Hi,

    As I above mentioned i got 1 idea and the query is written below

    SELECT count(Customer_id) FROM Customer WHERE CountryCode='vn'

    UNION ALL

    SELECT COUNT(salesOp_id) FROM SalesOp WHERE programCode='lt' and SalesOfficeCode like 'vn%'

    The above code displays the result as

    Nocolumnname

    --------------

    12453

    2321

    The result is correct but i need the result as below

    Customer 12453

    SalesOp 2321

    Could any 1 pls

    Thanks in advance

    anitha

  • November 20, 2007 at 5:32 am

    #752614

    Thanks a lot suresh and vaidyanathan.

    But i need the result as mentioned ,could u pls help

    Thanks,

    Anitha

  • November 20, 2007 at 5:32 am

    #752615

    Just add one more column in your select.....

    SELECT 'Customer' AS Table, count(Customer_id) FROM Customer WHERE CountryCode='vn'

    UNION ALL

    SELECT 'SalesOp' AS Table, COUNT(salesOp_id) FROM SalesOp WHERE programCode='lt' and SalesOfficeCode like 'vn%'

    --Ramesh

  • November 20, 2007 at 5:37 am

    #752617

    What about this??

    SELECT ' Customer' ,count(Customer_id) FROM Customer WHERE CountryCode='vn'

    UNION ALL

    SELECT 'SalesOp' ,COUNT(salesOp_id) FROM SalesOp WHERE programCode='lt' and SalesOfficeCode like 'vn%'

  • November 20, 2007 at 5:42 am

    #752620

    Thanks a lot Ramesh.

    It really helped me lot..

    Can any 1 give me the same result using joins

    Thanks,

    Anitha.

  • November 20, 2007 at 5:54 am

    #752626

    Same query by using joins.

    SELECT 'customer', count(Customer_id),'SalesOp',COUNT(salesOp_id) FROM Customer JOIN SalesOp ON Customer.CountryCode='vn' and

    (SalesOp.programCode='lt' and SalesOfficeCode like 'vn%')

    Regards,

    Naveen:)

Viewing 9 posts - 1 through 8 (of 8 total)

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