February 13, 2013 at 12:45 pm
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
February 13, 2013 at 12:52 pm
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.
February 17, 2013 at 3:16 am
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
February 18, 2013 at 6:54 am
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.
February 18, 2013 at 7:02 am
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