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!

score 0 · Accepted Answer · answered Mar 29 '18 at 15:22

0

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

answered Mar 29 '18 at 15:22

Mewtwo

1,231
2
18
38

THANK YOU SO MUCH! That is exactly what I needed! – Wendy Mar 30 '18 at 18:54
@Wendy Glad I helped :) If the answer solved your problem, mark it as accepted (by clicking the check sign on the top left of my answer) so that other will also know it – Mewtwo Mar 31 '18 at 13:18
Done! :) One follow up - how would I include a total at the bottom of each column - Total Agencies and New Agencies? – Wendy Apr 05 '18 at 13:30

Combining several queries into 1 new query

1 Answers1