How to display results from multiple WHERE selects in 1 query?

Hi Everyone,

I have a table called 'TS'
Also have 3 columns with the following values:
Article: 15, 34, 24, 73
History: 0, 21, 34, 44, 44, 34, 23
Report: 22, 43, 64, 32, 0, 0, 1, 0

And I'd like to display the result of the following:
SUM of Article, Distinct values of History, Count of 0 values in Report column in the following format:

SumOfArticle | 146
DistinctOfHistory | 0, 21, 34, 44, 23
Count0sReport | 3

And this one written in one query, I believe with UNION statement (but how does the group by would look like if it has?)

Thank you so much!

Please post directly usable sample data. That is, CREATE TABLE and INSERT statement(s).

1 Like

Sorry. I hope it's okay!:

drop table if exists #TS

create table #TS ( Article (int) , History (int), Report (int))
insert into #Article select '15', '34', '24', '73'
insert into #History select '0', '21', '34', '44', '44', '34', '23'
insert into #Report select '22', '43', '64', '32', '0','0', '1', '0'

hi

this is not working ...

did you test if first ?

1 Like

hi

hope this helps

i tried to guess ..

create data script

drop table if exists #TS
create table #TS ( Article int , History int , Report int )

insert into #TS ( Article ) select '15'
insert into #TS ( Article ) select '34'
insert into #TS ( Article ) select '24'
insert into #TS ( Article ) select '73'

insert into #TS (History ) select '0'
insert into #TS (History ) select '21'
insert into #TS (History ) select '34'
insert into #TS (History ) select '44'
insert into #TS (History ) select '44'
insert into #TS (History ) select '34'
insert into #TS (History ) select '23'

insert into #TS(Report) select '22'
insert into #TS(Report) select'43'
insert into #TS(Report) select'64'
insert into #TS(Report) select'32'
insert into #TS(Report) select'0'
insert into #TS(Report) select'0'
insert into #TS(Report) select'1'
insert into #TS(Report) select'0'

hi

hope this helps

another interpretation

create data script

drop table if exists #TS

create table #TS ( Article int , History int , Report int )

insert into #TS select '15', '0' , '22'
insert into #TS select '34','21','43'
insert into #TS select '24','34','64'
insert into #TS select '73','44','32'
insert into #TS select null, '44','0'
insert into #TS select null, '34','0'
insert into #TS select null, '23','1'
insert into #TS select null, null,'0'

;with cte as ( select distinct history from #TS  where history is not null)
   select 'DistinctOfHistory |', string_agg( history,',') from cte 
union all 
   select 'Sum of Article |' , cast(sum(cast(article as int)) as varchar) from #TS where article is not null
union all 
   select 'Count0sReport |', cast(sum(case when report = 0 then 1 else 0 end) as varchar) from #TS where report is not null