I have the following 2 queries that are almost identical except the second query contains a table join, where clause and has less FAXDEPTs(the commented out portion is included in Query 2 just shared the one query to make easier to read).
I want to join the two queries by FAXDEPT and have the results look something like this,
FAXDEPT| TOTAL DOCUMENTS(Query1)|TOTAL PAGES (Query1)|TOTAL DOCUMENTS(Query2)|TOTAL PAGES (Query2)
Query 1 contains more FAXDEPTs than Query2. Is there a way I can display "0"s for Query 2 TOTAL PAGES and TOTAL DOCS I'm assumed I would do some sort of FUll OUTER JOIN but can't seem to get it to work. Not sure if using alias is part of my issue or not. I appreciate all the help in advance!
select sq.faxdept 'Fax Department', count(sq.docid)'Total Documents', sum(sq.pages)'Total Pages'
from
(
select idp.id as docid, (MAX(idp.pagenum)+1) as pages, ki178.keyvaluechar as faxdept
from DOCDATA dd
left join FAXDEPT ki178 on id.id = ki178.id
left join IMPORTSOURCE ki228 on id.id = ki228.id
left join BATCHINFO kgd105 on dd.id = kgd105.id
left join PAGEDATA idp on id.id = idp.id
--left join QUEUE ilc on id.id = ilc.id
where
id.datestored > '10/07/2021' and id.status = 0
--and ilc.num = 252
and (kgd105.kg128 like 'DISC DIP%SJIN%' or ki228.keyvaluechar like 'SJIN' )
group by idp.id, ki178.keyvaluechar
)
as sq
group by sq.faxdept
