SQLTeam.com | Weblogs | Forums

How to do i merge these queries into one


#1

I am trying to merge these multiple queries into one with the columns displayed side by side however the columns rgn, xqtr need to be displayed once followed by the aliases like exiters, Q2ER, GenderM, GenderF etc. How do I go about doing this?


USE doleta

DECLARE @EER_BEGIN CHAR(8);
SET @EER_BEGIN = '20130701';

DECLARE @EER_END CHAR(8);
SET @EER_END = '20140630'; --2 qtr rate and median earnings

--DECLARE @EER_BEGIN4 CHAR(8);
-- SET @EER_BEGIN4 = '20130101';

--DECLARE @EER_END4 CHAR(8);
-- SET @EER_END4 = '20131231'; --4 qtr rate


select RGN,xqtr, count (*)Exiters
from dbo.WIA_9090_20144
where EXDTE BETWEEN @EER_BEGIN AND @EER_END
and (Adult='1' or adultstw='1')
and rgn between '01' and '24'
group by rgn,xqtr
order by rgn,xqtr

what I am trying to do here is take all these queries and merge into one with the rgn, xqtr used as only two columns, and the rest of columns being Q2ER

-- ADULTS EMPLOYED AT PARTICIPATION
-- FOR THE FEDERAL YEAR 10/01/2005 TO 09/30/2006
-- Run for exclusions and inclusions


USE doleta

DECLARE @EER_BEGIN CHAR(8);
SET @EER_BEGIN = '20130701';

DECLARE @EER_END CHAR(8);
SET @EER_END = '20140630'; --2 qtr rate and median earnings

--DECLARE @EER_BEGIN4 CHAR(8);
-- SET @EER_BEGIN4 = '20130101';

--DECLARE @EER_END4 CHAR(8);
-- SET @EER_END4 = '20131231'; --4 qtr rate


select RGN,xqtr, count (*)Exiters
from dbo.WIA_9090_20144
where EXDTE BETWEEN @EER_BEGIN AND @EER_END
and (Adult='1' or adultstw='1')
and rgn between '01' and '24'
group by rgn,xqtr
order by rgn,xqtr

select RGN,xqtr, count (*)Q2ER
from dbo.WIA_9090_20144
where EXDTE BETWEEN @EER_BEGIN AND @EER_END
and (Adult='1' or adultstw='1')
and (ExitWgeQtr2>0 or EmpQtr2='1')
and rgn between '01' and '24'
group by rgn,xqtr
order by rgn,xqtr

select RGN,xqtr, count (*)GenderM
from dbo.WIA_9090_20144
where EXDTE BETWEEN @EER_BEGIN AND @EER_END
and (Adult='1' or adultstw='1')
and gender='1'
and rgn between '01' and '24'
group by rgn,xqtr
order by rgn,xqtr

select RGN,xqtr, count (*)GenderF
from dbo.WIA_9090_20144
where EXDTE BETWEEN @EER_BEGIN AND @EER_END
and (Adult='1' or adultstw='1')
and gender='2'
and rgn between '01' and '24'
group by rgn,xqtr
order by rgn,xqtr

select RGN,xqtr, count (*)Age2635
from dbo.WIA_9090_20144
where EXDTE BETWEEN @EER_BEGIN AND @EER_END
and (Adult='1' or adultstw='1')
and age between 26 and 35
and rgn between '01' and '24'
group by rgn,xqtr
order by rgn,xqtr

select RGN,xqtr, count (*)Age3645
from dbo.WIA_9090_20144
where EXDTE BETWEEN @EER_BEGIN AND @EER_END
and (Adult='1' or adultstw='1')
and age between 36 and 45
and rgn between '01' and '24'
group by rgn,xqtr
order by rgn,xqtr

select RGN,xqtr, count (*)Age4655
from dbo.WIA_9090_20144
where EXDTE BETWEEN @EER_BEGIN AND @EER_END
and (Adult='1' or adultstw='1')
and age between 46 and 55
and rgn between '01' and '24'
group by rgn,xqtr
order by rgn,xqtr

select RGN,xqtr, count (*)Age5665
from dbo.WIA_9090_20144
where EXDTE BETWEEN @EER_BEGIN AND @EER_END
and (Adult='1' or adultstw='1')
and age between 56 and 65
and rgn between '01' and '24'
group by rgn,xqtr
order by rgn,xqtr

select RGN,xqtr, count (*)Age66
from dbo.WIA_9090_20144
where EXDTE BETWEEN @EER_BEGIN AND @EER_END
and (Adult='1' or adultstw='1')
and age >=66
and rgn between '01' and '24'
group by rgn,xqtr
order by rgn,xqtr

select RGN,xqtr, count (*)RaceHisp
from dbo.WIA_9090_20144
where EXDTE BETWEEN @EER_BEGIN AND @EER_END
and (Adult='1' or adultstw='1')
and Ethnic='1'
and rgn between '01' and '24'
group by rgn,xqtr
order by rgn,xqtr

select RGN,xqtr, count (*)RaceAsian
from dbo.WIA_9090_20144
where EXDTE BETWEEN @EER_BEGIN AND @EER_END
and (Adult='1' or adultstw='1')
and Asian='1'
and rgn between '01' and '24'
group by rgn,xqtr
order by rgn,xqtr

select RGN,xqtr, count (*)RaceBlack
from dbo.WIA_9090_20144
where EXDTE BETWEEN @EER_BEGIN AND @EER_END
and (Adult='1' or adultstw='1')
and Black='1'
and rgn between '01' and '24'
group by rgn,xqtr
order by rgn,xqtr

select RGN,xqtr, count (*)RaceHPI
from dbo.WIA_9090_20144
where EXDTE BETWEEN @EER_BEGIN AND @EER_END
and (Adult='1' or adultstw='1')
and Hawaiian='1'
and rgn between '01' and '24'
group by rgn,xqtr
order by rgn,xqtr

select RGN,xqtr, count (*)RaceAI
from dbo.WIA_9090_20144
where EXDTE BETWEEN @EER_BEGIN AND @EER_END
and (Adult='1' or adultstw='1')
and NativeAmerican='1'
and rgn between '01' and '24'
group by rgn,xqtr
order by rgn,xqtr

select RGN,xqtr, count (*)RaceWht
from dbo.WIA_9090_20144
where EXDTE BETWEEN @EER_BEGIN AND @EER_END
and (Adult='1' or adultstw='1')
and White='1'
and rgn between '01' and '24'
group by rgn,xqtr
order by rgn,xqtr

select RGN,xqtr, count (*)RaceMult
from dbo.WIA_9090_20144
where EXDTE BETWEEN @EER_BEGIN AND @EER_END
and (Adult='1' or adultstw='1')
and
(
(NativeAmerican='1') and (Asian='1' or Black='1' or Hawaiian='1' or White='1')
or (Asian='1') and (NativeAmerican='1' or Black='1' or Hawaiian='1' or White='1')
or (Black='1') and (NativeAmerican='1' or Asian='1' or Hawaiian='1' or White='1')
or (Hawaiian='1') and (NativeAmerican='1' or Asian='1' or Black='1' or White='1')
or (White='1') and (NativeAmerican='1' or Asian='1' or Black='1' or Hawaiian='1')
)
and rgn between '01' and '24'
group by rgn,xqtr
order by rgn,xqtr

select RGN,xqtr, count (*)hsdropout
from dbo.WIA_9090_20144
where EXDTE BETWEEN @EER_BEGIN AND @EER_END
and (Adult='1' or adultstw='1')
and HighGrd in ('87','88')
and rgn between '01' and '24'
group by rgn,xqtr
order by rgn,xqtr

select RGN,xqtr, count (*)hsgrad
from dbo.WIA_9090_20144
where EXDTE BETWEEN @EER_BEGIN AND @EER_END
and (Adult='1' or adultstw='1')
and HighGrd between '00' and '12'
and rgn between '01' and '24'
group by rgn,xqtr
order by rgn,xqtr

select RGN,xqtr, count (*)collegedropout
from dbo.WIA_9090_20144
where EXDTE BETWEEN @EER_BEGIN AND @EER_END
and (Adult='1' or adultstw='1')
and HighGrd in ('13','14','15')
and rgn between '01' and '24'
group by rgn,xqtr
order by rgn,xqtr

select RGN,xqtr, count (*)certotherps
from dbo.WIA_9090_20144
where EXDTE BETWEEN @EER_BEGIN AND @EER_END
and (Adult='1' or adultstw='1')
and HighGrd='90'
and rgn between '01' and '24'
group by rgn,xqtr
order by rgn,xqtr

select RGN,xqtr, count (*)assoc
from dbo.WIA_9090_20144
where EXDTE BETWEEN @EER_BEGIN AND @EER_END
and (Adult='1' or adultstw='1')
and HighGrd='91'
and rgn between '01' and '24'
group by rgn,xqtr
order by rgn,xqtr

select RGN,xqtr, count (*)ba
from dbo.WIA_9090_20144
where EXDTE BETWEEN @EER_BEGIN AND @EER_END
and (Adult='1' or adultstw='1')
and HighGrd='16'
and rgn between '01' and '24'
group by rgn,xqtr
order by rgn,xqtr

select RGN,xqtr, count (*)EmpParticipation
from dbo.WIA_9090_20144
where EXDTE BETWEEN @EER_BEGIN AND @EER_END
and (Adult='1' or adultstw='1')
and EmpStat='1'
and rgn between '01' and '24'
group by rgn,xqtr
order by rgn,xqtr

select RGN,xqtr, count (*)Dis
from dbo.WIA_9090_20144
where EXDTE BETWEEN @EER_BEGIN AND @EER_END
and (Adult='1' or adultstw='1')
and Dsble='1'
and rgn between '01' and '24'
group by rgn,xqtr
order by rgn,xqtr

select RGN,xqtr, count (*)Veteran
from dbo.WIA_9090_20144
where EXDTE BETWEEN @EER_BEGIN AND @EER_END
and (Adult='1' or adultstw='1')
and VetStat='1'
and rgn between '01' and '24'
group by rgn,xqtr
order by rgn,xqtr

select RGN,xqtr, count (*)WageP3P2
from dbo.WIA_9090_20144
where EXDTE BETWEEN @EER_BEGIN AND @EER_END
and (Adult='1' or adultstw='1')
and (PriorWgeQtr2>0 and PriorWgeQtr3>0)
and rgn between '01' and '24'
group by rgn,xqtr
order by rgn,xqtr

select RGN,xqtr, count (*)WageP3
from dbo.WIA_9090_20144
where EXDTE BETWEEN @EER_BEGIN AND @EER_END
and (Adult='1' or adultstw='1')
and (PriorWgeQtr3>0)
and rgn between '01' and '24'
group by rgn,xqtr
order by rgn,xqtr

select RGN,xqtr, count (*)WageP2
from dbo.WIA_9090_20144
where EXDTE BETWEEN @EER_BEGIN AND @EER_END
and (Adult='1' or adultstw='1')
and (PriorWgeQtr2>0)
and rgn between '01' and '24'
group by rgn,xqtr
order by rgn,xqtr

select RGN,xqtr, count (*)WP
from dbo.WIA_9090_20144
where EXDTE BETWEEN @EER_BEGIN AND @EER_END
and (Adult='1' or adultstw='1')
and WP_ACT='1'
and rgn between '01' and '24'
group by rgn,xqtr
order by rgn,xqtr

select RGN,xqtr, count (*)LimEng
from dbo.WIA_9090_20144
where EXDTE BETWEEN @EER_BEGIN AND @EER_END
and (Adult='1' or adultstw='1')
and LtdEnglish='1'
and rgn between '01' and '24'
group by rgn,xqtr
order by rgn,xqtr

select RGN,xqtr, count (*)SinglePar
from dbo.WIA_9090_20144
where EXDTE BETWEEN @EER_BEGIN AND @EER_END
and (Adult='1' or adultstw='1')
and SngleParent='1'
and rgn between '01' and '24'
group by rgn,xqtr
order by rgn,xqtr

select RGN,xqtr, count (*)LowInc
from dbo.WIA_9090_20144
where EXDTE BETWEEN @EER_BEGIN AND @EER_END
and (Adult='1' or adultstw='1')
and LowIncme='1'
and rgn between '01' and '24'
group by rgn,xqtr
order by rgn,xqtr

select RGN,xqtr, count (*)RecTanf
from dbo.WIA_9090_20144
where EXDTE BETWEEN @EER_BEGIN AND @EER_END
and (Adult='1' or adultstw='1')
and TANF='1'
and rgn between '01' and '24'
group by rgn,xqtr
order by rgn,xqtr

select RGN,xqtr, count (*)RecOtherGov
from dbo.WIA_9090_20144
where EXDTE BETWEEN @EER_BEGIN AND @EER_END
and (Adult='1' or adultstw='1')
and OthrPubAsst='1'
and rgn between '01' and '24'
group by rgn,xqtr
order by rgn,xqtr

select RGN,xqtr, count (*)Homeless
from dbo.WIA_9090_20144
where EXDTE BETWEEN @EER_BEGIN AND @EER_END
and (Adult='1' or adultstw='1')
and HmlessRunaway='1'
and rgn between '01' and '24'
group by rgn,xqtr
order by rgn,xqtr

select RGN,xqtr, count (*)Offender
from dbo.WIA_9090_20144
where EXDTE BETWEEN @EER_BEGIN AND @EER_END
and (Adult='1' or adultstw='1')
and Offender='1'
and rgn between '01' and '24'
group by rgn,xqtr
order by rgn,xqtr

select RGN,xqtr, count (*)UIClaimant
from dbo.WIA_9090_20144
where EXDTE BETWEEN @EER_BEGIN AND @EER_END
and (Adult='1' or adultstw='1')
and UC in ('1','2')
and rgn between '01' and '24'
group by rgn,xqtr
order by rgn,xqtr

select RGN,xqtr, count (*)UIExhaustee
from dbo.WIA_9090_20144
where EXDTE BETWEEN @EER_BEGIN AND @EER_END
and (Adult='1' or adultstw='1')
and UC='3'
and rgn between '01' and '24'
group by rgn,xqtr
order by rgn,xqtr

select RGN,xqtr, count (*)RecSuppServ
from dbo.WIA_9090_20144
where EXDTE BETWEEN @EER_BEGIN AND @EER_END
and (Adult='1' or adultstw='1')
and SupportServ='1'
and rgn between '01' and '24'
group by rgn,xqtr
order by rgn,xqtr

select RGN,xqtr, count (*)RecNeeds
from dbo.WIA_9090_20144
where EXDTE BETWEEN @EER_BEGIN AND @EER_END
and (Adult='1' or adultstw='1')
and NeedRelated='1'
and rgn between '01' and '24'
group by rgn,xqtr
order by rgn,xqtr

select RGN,xqtr, count (*)RecInt
from dbo.WIA_9090_20144
where EXDTE BETWEEN @EER_BEGIN AND @EER_END
and (Adult='1' or adultstw='1')
and FrstDteIntensive is not null
and rgn between '01' and '24'
group by rgn,xqtr
order by rgn,xqtr

select RGN,xqtr, count (*)RecTrain
from dbo.WIA_9090_20144
where EXDTE BETWEEN @EER_BEGIN AND @EER_END
and (Adult='1' or adultstw='1')
and TrainStrtDte1 is not null
and rgn between '01' and '24'
group by rgn,xqtr
order by rgn,xqtr

select RGN,xqtr, count (*)RecITA
from dbo.WIA_9090_20144
where EXDTE BETWEEN @EER_BEGIN AND @EER_END
and (Adult='1' or adultstw='1')
and ITA='1'
and rgn between '01' and '24'
group by rgn,xqtr
order by rgn,xqtr

select RGN,xqtr, count (*)RecPell
from dbo.WIA_9090_20144
where EXDTE BETWEEN @EER_BEGIN AND @EER_END
and (Adult='1' or adultstw='1')
and Pell='1'
and rgn between '01' and '24'
group by rgn,xqtr
order by rgn,xqtr

select RGN,xqtr, count (*)RecPreVoc
from dbo.WIA_9090_20144
where EXDTE BETWEEN @EER_BEGIN AND @EER_END
and (Adult='1' or adultstw='1')
and PreVoc='1'
and rgn between '01' and '24'
group by rgn,xqtr
order by rgn,xqtr


#2

Try using CASE and SUM like:

SELECT rgn, xqtr, COUNT (*) AS Exiters
    ,SUM(CASE WHEN (ExitWgeQtr2>0 or EmpQtr2='1') THEN 1 ELSE 0 END) AS Q2ER
    ,SUM(CASE WHEN gender='1' THEN 1 ELSE 0 END) AS GenderM
    ,SUM(CASE WHEN (age between 26 and 35) THEN 1 ELSE 0 END) AS Age2635
    ,SUM(CASE WHEN Asian='1' THEN 1 ELSE 0 END) AS RaceAsian
    ,SUM(CASE WHEN (HighGrd in ('87','88')) THEN 1 ELSE 0 END) AS hsdropout
    -- etc
FROM dbo.WIA_9090_20144
WHERE EXDTE BETWEEN @EER_BEGIN AND @EER_END
    AND (Adult='1' OR adultstw='1')
    AND rgn BETWEEN '01' AND '24'
GROUP BY rgn,xqtr
ORDER BY rgn,xqtr;

#3

This works. Thank you so much. You are a life saver.