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

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2008 » T-SQL (SS2K8) » combining the results of multiple select...

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

Rate Topic

Display Mode

Topic Options

Author

Message

sandhyarao49

Posted Wednesday, February 13, 2013 12:45 PM

SSC-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

toddasd

Posted Wednesday, February 13, 2013 12:52 PM

SSC-Addicted

Group: General Forum Members
Last Login: Thursday, May 09, 2013 2:42 PM
Points: 477, Visits: 3,649

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

sandhyarao49

Posted Sunday, February 17, 2013 3:15 AM

SSC-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

toddasd

Posted Monday, February 18, 2013 6:54 AM

SSC-Addicted

Group: General Forum Members
Last Login: Thursday, May 09, 2013 2:42 PM
Points: 477, Visits: 3,649

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

Gabriel P

Posted Monday, February 18, 2013 7:02 AM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, May 06, 2013 6:28 AM
Points: 165, Visits: 620

sandhyarao49 (2/13/2013)

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.

MCITP - SQL Developer

Post #1421200

« Prev Topic | Next Topic »

Permissions