Combining several queries into 1 new query

Question

I have a table that consists of all of our agency records. I have several queries set up that count something specific about these records and each query groups them all by date. What I am trying to figure out is how I can combine these queries into one new query. Right now, I run each one, put them into Excel and then do a vlookup and combine them into one. Here are just two of my queries.

Query #1:

select 
    LocationStateAbr,
    count(LocationStateAbr) as "Total Agencies" 
from
    [PROD_Agency].[dbo].[AgAgency]
where 
    StatusId = ' ' 
    and BusinessId in ('b', 'C') 
    and TypeId in ('A', 'C', 'F', 'I', 'X') 
group by 
    LocationStateAbr
order by 
    LocationStateAbr ASC

Query #2:

select 
    LocationStateAbr,
    count(LocationStateAbr) as "New Agencies" 
from
    [PROD_Agency].[dbo].[AgAgency]
where 
    year(AppointedDt) = 2018 
    and StatusId = ' ' 
    and BusinessId in ('b', 'C') 
    and TypeId in ('A', 'C', 'F', 'I', 'X') 
group by 
    LocationStateAbr
order by 
    LocationStateAbr ASC

Any suggestions? Thank you!

Answer 1

You can combine the two queries into one using CASE. In your case it would be something like this:

select 
    LocationStateAbr,
    count(case when StatusId = ' ' 
          and BusinessId in ('b', 'C') 
          and TypeId in ('A', 'C', 'F', 'I', 'X') then 1 else null end) as "Total Agencies",
    count(case when year(AppointedDt) = 2018 
          and StatusId = ' ' 
          and BusinessId in ('b', 'C') 
          and TypeId in ('A', 'C', 'F', 'I', 'X') the 1 else null end) as "New Agencies"
FROM
   [PROD_Agency].[dbo].[AgAgency]
group by 
    LocationStateAbr
order by 
    LocationStateAbr ASC