CODEWITHSUNDEEP
sql-server
Arantuath
Arantuath

Reputation: 243

Merge two select statements from same table?

I want to create a query which merges the results of two queries from the same table.

select
(select count(distinct(Case_ID)) as expr1 from  dbo.Cases)
(select count(distinct(Case_ID)) as expr2 from dbo.Cases where Case_Status = 'Open')

The above produces two sets of results, what i want is a single set of results with expr1 & expr2 as two separate columns. I will later use this result in SRSS.

Upvotes: 1

Views: 2251

Answers (3)

Gordon Linoff
Gordon Linoff

Reputation: 1271003

If you really need to count distinct cases:

select count(distinct case_id) as NumCases, 
       count(case when Case_Status = 'Open' then case_id end) as NumOpen
from dbo.Cases;

Note: if Case_Id is unique in Cases, then the distinct is not necessary. However, you have it in the original query, suggesting this may not be the case. The distinct performs worse than a regular count() or sum().

Upvotes: 1

eglasius
eglasius

Reputation: 36035

select 
     count(*) as expr1, 
     sum(case when Case_Status = 'Open' then 1 else 0 end)
from dbo.Cases

Upvotes: 2

asantaballa
asantaballa

Reputation: 4048

Looks like you are preety close. This should work

select
  (select count(distinct(Case_ID)) as expr1 from  dbo.Cases)
, (select count(distinct(Case_ID)) as expr2 from dbo.Cases where Case_Status = 'Open'

May just be missing the comma between the two fields?

Upvotes: 1

Related Questions