CODEWITHSUNDEEP
sqlsql-server-2008
sooprise
sooprise

Reputation: 23187

Getting multiple variables to show up in sql server?

I have two queries:

Select count(*) as countOne where field = '1'
Select count(*) as countTwo where field = '2'

What I want to see after executing these queries in my results viewer:

countOne | countTwo
      23 |      123

How can I get the results from both queries by only running one query?

Upvotes: 1

Views: 81

Answers (2)

Martin Smith
Martin Smith

Reputation: 453453

SELECT COUNT(CASE WHEN field = '1' THEN 1 END) AS countOne,
       COUNT(CASE WHEN field = '2' THEN 1 END) AS countTwo
FROM   YourTable
WHERE  field IN ( '1', '2' )

Upvotes: 4

Matt Fellows
Matt Fellows

Reputation: 6532

The simplest way is to run each as a subselect eg.

SELECT 
(
Select count(*) where field = '1' as countOne,
Select count(*) where field = '2' as countTwo
)

BUt this is not necesarily the best way

Another wayto do it would be to Group by field and then do PIVOT to select out each group as a separate column.

Upvotes: 0

Related Questions