Search code examples
sqlsql-servercountcross-join

Join Two Count(*) Tables with No Relation in SQL Server

I am trying to combine the results of a count(*) statement and a count(*) with a where clause on a SQL Server Table into a single table.

I have a union statement that bring together the two queries one of top of another.

SELECT count(*) FROM [dbo].asma a
where [MLR] in ('y')) l
union
SELECT count (*) as 'Total' FROM [dbo].asma]

Union Return

This post of solutions I looked at, but couldn't piece together a solution that would present these side by side. How would you do this?

What I need is this output:

enter image description here

Solution

  • You can do conditional aggregation instead :

    select sum(case when MLR = 'y' then 1 else 0 end) as Active, count(*) as Total
from dbo.asma a;