Combining two different types of count

Databases
1

What is the best way to combine these two queries into one?


select count(*) as scheduled from notifications where date_format(dateScheduled, '%Y-%m-%d')  = curdate();


select count(*) as delivered from notifications where date_format(dateScheduled, '%Y-%m-%d')  = curdate() and dateSent is not null;

Thanks!

2

Nevermind. I think this is working good for me:


select 
	
	(select count(*) from notifications where date_format(dateScheduled, '%Y-%m-%d')  = curdate()) as scheduled,


	(select count(*) from notifications where date_format(dateScheduled, '%Y-%m-%d')  = curdate() and dateSent is not null) as delivered


FROM notifications group by scheduled, delivered;
3

while that does work, it’s not the most efficient approach

a simpler method is this –

 WHERE DATE(dateScheduled) = CURRENT_DATE()

however, this too suffers from an efficiency problem

using a function on a table column pretty much rules out index optimization, and the database optimizer will often just do a table scan

here is a much better approach that will utilize an index on the column –

 WHERE dateScheduled >= CURRENT_DATE()
   AND dateScheduled  < CURRENT_DATE() + INTERVAL 1 DAY

you also used two complete passes of the data, whereas you really only need one –

SELECT COUNT(*) AS scheduled
     , COUNT(dateSent) AS delivered
  FROM notifications 
 WHERE dateScheduled >= CURRENT_DATE()
   AND dateScheduled  < CURRENT_DATE() + INTERVAL 1 DAY

:slight_smile:

4

Thanks again Rudy!