Styjun

I'm working on two queries that pull data from one table. I can get them to run how I want individually, but I'd like to combine them into one if possible. I've tried the solution in [Multiple COUNT SELECTS from the same table in one query but wasn't able to get it figured out. Below is what I'm working on:

--goback 30 days start at midnight
declare @daysgoingback as int
set @daysgoingback = 90

declare @olderdate as datetime
set @olderdate = DATEADD(Day, -@daysgoingback, DATEDIFF(Day, 0, GetDate()))

--today at 11:59pm
declare @today as datetime
set @today = dateadd(ms, -3, (dateadd(day, +1, convert(varchar, GETDATE(), 101))))
print @today

--these are the two queries I'd like to combine:
select 
 avg(x.LogAlerts*1.0 / @daysgoingback) as 'Avg number of log alerts'
from
 (select count(*) as LogAlerts 
 from message_log_table 
 where msg_timestamp between @olderdate and @today) X

select 
 avg(x.MessagesDeleted*1.0 / @daysgoingback) as 'Avg number of messages deleted'
from
 (select count(*) as MessagesDeleted 
 from message_log_table 
 where msg_details like '%message deleted%' 
 and msg_timestamp between @olderdate and @today) X

"AVG" is an aggregate function, which averages multiple rows. Your "select count(*)" subquery always returns exactly one row, so you are not really using the AVG function as it's intended.

You can combine the two like this:

 select count(*) * 1.0 / @daysgoingback as 'Avg number of log alerts'
 , count(case when msg_details like '%message deleted%' then 1 end) * 1.0 / @daysgoingback as 'Avg number of messages deleted'
 from message_log_table 
 where msg_timestamp between @olderdate and @today;

Make each query into a CTE, and then CROSS JOIN them.

Both queries only return a single-cell result, so there's no need to get fancy here.