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.