How do I merge two SELECT queries with different WHERE clauses

I’m trying to write query on two different selects with different WHERE clause and using GROUP BY. I browsed for examples but mine is different since I have multiple fields for SELECT. Here is example data:

--    drop table #temp_counts;
create table #temp_counts(
report_date smalldatetime not null, 
Emp_id varchar(10) not null,
source_system varchar(10) not null
)

Insert into #temp_counts values ('2021-05-02 00:00:00', '12411', 'ABC');
Insert into #temp_counts values ('2021-05-02 00:00:00', '56421', 'ABC');
Insert into #temp_counts values ('2021-05-02 00:00:00', '45411', 'ABC');
Insert into #temp_counts values ('2021-05-02 00:00:00', '75411', 'ABC');
Insert into #temp_counts values ('2021-05-02 00:00:00', '13245', 'XYZ');
Insert into #temp_counts values ('2021-05-02 00:00:00', '66245', 'XYZ');
Insert into #temp_counts values ('2021-05-02 00:00:00', '77245', 'XYZ');
Insert into #temp_counts values ('2021-05-02 00:00:00', '98245', 'XYZ');
Insert into #temp_counts values ('2021-05-02 00:00:00', '34245', 'XYZ');
Insert into #temp_counts values ('2021-05-02 00:00:00', '29245', 'XYZ');

Insert into #temp_counts values ('2021-05-03 00:00:00', '14524', 'ABC');
Insert into #temp_counts values ('2021-05-03 00:00:00', '17824', 'ABC');
Insert into #temp_counts values ('2021-05-03 00:00:00', '32524', 'ABC');
Insert into #temp_counts values ('2021-05-03 00:00:00', '16724', 'XYZ');
Insert into #temp_counts values ('2021-05-03 00:00:00', '19924', 'XYZ');
Insert into #temp_counts values ('2021-05-03 00:00:00', '89424', 'XYZ');
Insert into #temp_counts values ('2021-05-03 00:00:00', '48324', 'XYZ');
Insert into #temp_counts values ('2021-05-03 00:00:00', '16000', 'XYZ');

Insert into #temp_counts values ('2021-05-04 00:00:00', '18724', 'ABC');
Insert into #temp_counts values ('2021-05-04 00:00:00', '12904', 'XYZ');

Insert into #temp_counts values ('2021-05-05 00:00:00', '12074', 'ABC');
Insert into #temp_counts values ('2021-05-05 00:00:00', '12784', 'XYZ');
Insert into #temp_counts values ('2021-05-05 00:00:00', '12324', 'XYZ');
Insert into #temp_counts values ('2021-05-05 00:00:00', '75124', 'XYZ');

These are the queries I would like to merge:

select count(*) emp_count,  report_date , 'ABC' source_system from #temp_counts 
where  source_system = 'ABC'
group by report_date
order by report_date

select count(*) emp_count,  report_date , 'XYZ' source_system from #temp_counts 
where  source_system = 'XYZ'
group by report_date
order by report_date

I tried 2 methods as under:

--Method 1
select fir.emp_count, fir.report_date, fir.source_system from
(select count(*) emp_count,  report_date , 'ABC' source_system from #temp_counts 
where  source_system = 'ABC') as fir
inner join
(select count(*) emp_count,  report_date , 'XYZ' source_system from #temp_counts 
where  source_system = 'XYZ') as sec
on fir.report_date = sec.report_date
group by fir.report_date
order by fir.report_date
--Method 2
select count(*) emp_count,  report_date , 'ABC' source_system from #temp_counts 
where  source_system = 'ABC'
UNION ALL
select count(*) emp_count,  report_date , 'XYZ' source_system from #temp_counts 
where  source_system = 'XYZ'
group by report_date
order by report_date

Both give Error:

Msg 8120, Level 16, State 1, Line 61
Column '#temp_counts.report_date' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

Please guide

Answer

seems like you just want to group by report_date and source_system:

select count(*) emp_count,  report_date , source_system 
FROM #temp_counts
group by report_date, source_system
order by source_system,report_date

if you want to have result for specific source systems then you can combine conditions:

select count(*) emp_count,  report_date , source_system 
FROM #temp_counts 
where  source_system in ('ABC', 'XYZ')
group by report_date, source_system
order by source_system,report_date

you can change order by to show rows in the order you want to show

just to illustrate how to use union :

select count(*) emp_count,  report_date , source_system from #temp_counts 
where  source_system = 'ABC'
group by report_date

union all

select count(*) emp_count,  report_date , source_system from #temp_counts 
where  source_system = 'XYZ'
group by report_date
order by source_system, report_date

Advertisement

Answer