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

Transact-SQL
1

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!

2

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

1 Like
3

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'

4

hi

this is not working ...

did you test if first ?

1 Like
5

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'

image
image642×623 8.99 KB

6

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

image
image665×518 10.1 KB