SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


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


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

Author
Message
sandhyarao49
sandhyarao49
SSC Veteran
SSC Veteran (215 reputation)SSC Veteran (215 reputation)SSC Veteran (215 reputation)SSC Veteran (215 reputation)SSC Veteran (215 reputation)SSC Veteran (215 reputation)SSC Veteran (215 reputation)SSC Veteran (215 reputation)

Group: General Forum Members
Points: 215 Visits: 650
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
toddasd
toddasd
SSChasing Mays
SSChasing Mays (624 reputation)SSChasing Mays (624 reputation)SSChasing Mays (624 reputation)SSChasing Mays (624 reputation)SSChasing Mays (624 reputation)SSChasing Mays (624 reputation)SSChasing Mays (624 reputation)SSChasing Mays (624 reputation)

Group: General Forum Members
Points: 624 Visits: 3818
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.
sandhyarao49
sandhyarao49
SSC Veteran
SSC Veteran (215 reputation)SSC Veteran (215 reputation)SSC Veteran (215 reputation)SSC Veteran (215 reputation)SSC Veteran (215 reputation)SSC Veteran (215 reputation)SSC Veteran (215 reputation)SSC Veteran (215 reputation)

Group: General Forum Members
Points: 215 Visits: 650
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
toddasd
toddasd
SSChasing Mays
SSChasing Mays (624 reputation)SSChasing Mays (624 reputation)SSChasing Mays (624 reputation)SSChasing Mays (624 reputation)SSChasing Mays (624 reputation)SSChasing Mays (624 reputation)SSChasing Mays (624 reputation)SSChasing Mays (624 reputation)

Group: General Forum Members
Points: 624 Visits: 3818
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.
Gabriel P
Gabriel P
SSC-Addicted
SSC-Addicted (414 reputation)SSC-Addicted (414 reputation)SSC-Addicted (414 reputation)SSC-Addicted (414 reputation)SSC-Addicted (414 reputation)SSC-Addicted (414 reputation)SSC-Addicted (414 reputation)SSC-Addicted (414 reputation)

Group: General Forum Members
Points: 414 Visits: 947
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.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search