Q:

getting/optimizing counts with various conditions and tables

I am trying to return counts for different conditions and tables in one call.  I previously had each one of these in its own subquery, but realized there was no point in querying the realtime_logs table 4 times... so I changed it to the stmt below which works as expected.
$stmt = $db->prepare("
    SELECT
        sum(case when event_title = 'opened' then 1 end) as opened, 
        sum(case when event_title = 'closed' then 1 end) as closed,
        sum(case when event_title = 'viewed' then 1 end) as viewed,
        sum(case when event_title LIKE '%blocked%' then 1 end) as blocked 
    FROM realtime_logs
");
$stmt->execute();

$results = $stmt->fetchAll(PDO::FETCH_ASSOC);

Doing this though I lost the ability to get counts from other tables though... I want something like this...
$stmt = $db->prepare("
        SELECT
            sum(case when event_title = 'opened' then 1 end) as opened, 
            sum(case when event_title = 'closed' then 1 end) as closed,
            sum(case when event_title = 'viewed' then 1 end) as viewed,
            sum(case when event_title LIKE '%blocked%' then 1 end) as blocked 
        FROM realtime_logs

       // I also want to return the count for this different table

       (SELECT COUNT(location_id)
        FROM spc_location_logs
       ) as locations
");

A:

Why cant you simply split into two statement/queries? You can use PHP after to merge returned values into one array. Try this:
$stmt1 = $db->prepare("
    SELECT
        sum(case when event_title = 'opened' then 1 end) as opened, 
        sum(case when event_title = 'closed' then 1 end) as closed,
        sum(case when event_title = 'viewed' then 1 end) as viewed,
        sum(case when event_title LIKE '%blocked%' then 1 end) as blocked,
    FROM realtime_logs
");

$stmt2 = $db->prepare('
    SELECT COUNT(location_id) AS locations
    FROM spc_location_logs
');

$stmt1->execute();
$stmt2->execute();

$arr1 = $stmt1->fetchAll(PDO::FETCH_KEY_PAIR);
$arr2 = $stmt2->fetchAll(PDO::FETCH_KEY_PAIR);

$arr  = array_merge($arr1, $arr2);

Note im Using FETCH_KEY_PAIR mode.

