Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

combining the results of multiple select statements in to a single row Expand / Collapse
Author
Message
Posted Wednesday, February 13, 2013 12:45 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, February 18, 2013 9:50 PM
Points: 119, 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
Post #1419709
Posted Wednesday, February 13, 2013 12:52 PM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Monday, November 11, 2013 3:13 PM
Points: 477, Visits: 3,671
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.
Post #1419711
Posted Sunday, February 17, 2013 3:15 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, February 18, 2013 9:50 PM
Points: 119, 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
Post #1420954
Posted Monday, February 18, 2013 6:54 AM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Monday, November 11, 2013 3:13 PM
Points: 477, Visits: 3,671
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.
Post #1421194
Posted Monday, February 18, 2013 7:02 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Wednesday, April 16, 2014 6:59 AM
Points: 226, Visits: 766
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.
Post #1421200
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse