CODEWITHSUNDEEP
sql-server
dutchlab
dutchlab

Reputation: 590

How to Turn Multiple Union Statements into a Single Row in SQL Server

How would I turn this query into a single row with different column names.

 Select count(distinct accountid) as x
 from table1
 where active = 1
 and expiredate >= GetDate()
 and acceptedon is not null
 union
 Select count(distinct accountid) as x
 from table1
 where active = 1
 and expiredate <= GetDate()
 and acceptedon is not null

I get

x

5

4

I would like 

x    y

5    4

Upvotes: 0

Views: 41

Answers (1)

Yogesh Sharma
Yogesh Sharma

Reputation: 50163

You want simple conditional aggregation : 

Select count(distinct case when expiredate >= GetDate() then accountid end) as x,
       count(distinct case when expiredate <= GetDate() then accountid end) as y
from table1
where active = 1 and acceptedon is not null;

Upvotes: 3

Related Questions