combining the results of multiple select statements in to a single row

  • All,

    I have a requirement to combine the results of multiple select statements in to a single row.

    EX :

    query 1 : select count (*) as Col1 from abc where (conditions) : Result 10

    Query 2 : Select Count (*) as Col2 from abc where (Conditions) : result 11

    Query 3 : Select Count (*) as Col3 from abc where (Conditions) : result 12

    My requirement is to add the 3 results in to a single row as

    Col1 Col2 col3

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

    10 11 12

    Can any one give some suggestions around this as my where conditions are very complex in nature and i have to add 8- 9 queries in to a single row

  • Use cross apply, like so

    select * from

    (select count (*) as Col1 from sys.columns where (column_id = 1)) x

    cross apply

    (Select Count (*) as Col2 from sys.columns where (column_id = 2)) y

    cross apply

    (Select Count (*) as Col3 from sys.columns where (column_id = 3)) z

    ______________________________________________________________________________
    How I want a drink, alcoholic of course, after the heavy lectures involving quantum mechanics.

  • Thanks for the help.

    My output now is :

    10 , 11 , 12, 13

    Now i want another column as the sum of above values. How can i do that

  • As a quick and dirty, I would do this:

    with counts as (

    select * from

    (select count (*) as Col1 from sys.columns where (column_id = 1)) x

    cross apply

    (Select Count (*) as Col2 from sys.columns where (column_id = 2)) y

    cross apply

    (Select Count (*) as Col3 from sys.columns where (column_id = 3)) z

    )

    select Col1, Col2, Col3, Col1+Col2+Col3 as SumOfColumns from counts

    ______________________________________________________________________________
    How I want a drink, alcoholic of course, after the heavy lectures involving quantum mechanics.

  • sandhyarao49 (2/13/2013)


    All,

    I have a requirement to combine the results of multiple select statements in to a single row.

    EX :

    query 1 : select count (*) as Col1 from abc where (conditions) : Result 10

    Query 2 : Select Count (*) as Col2 from abc where (Conditions) : result 11

    Query 3 : Select Count (*) as Col3 from abc where (Conditions) : result 12

    My requirement is to add the 3 results in to a single row as

    Col1 Col2 col3

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

    10 11 12

    Can any one give some suggestions around this as my where conditions are very complex in nature and i have to add 8- 9 queries in to a single row

    If the table is large, in addition to the queries above, try this as well and see which one performs the best

    with t1 as (

    SELECT SUM(CASE WHEN (Conditions1) THEN 1 ELSE 0 END) Col1,

    SUM(CASE WHEN (Conditions2) THEN 1 ELSE 0 END) Col2,

    SUM(CASE WHEN (Conditions3) THEN 1 ELSE 0 END) Col3

    FROM abc)

    select *, Col1 + Col2 + Col3 FROM t1

    If the table is large, you may still want to add a where clause if you can find an index filter that prevents you from scanning the whole table, but still allows you to access all the rows that meet your requirements.

    Without knowing your table, indexes, data, and business requirements, I can't tell you much more than that.

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

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