getting row counts from multiple tables

Question

I am trying to get row counts returned for different tables based on user_id value. users is a table of all users with a unique column of user_id. All other tables have a corresponding user_id column to join on it with.

I would think this would be fairly easy, but for some reason I cannot get the counts to return right.

What I want to accomplish is alerts = ? and locations = ? where ? is the total number of rows in that table where user_id = 1,2,3,4,5,6,7, or 8.

$stmt = $db->prepare("
    SELECT
        count(t_alerts.user_id) as alerts,
        count(t_locations.user_id) as locations
    FROM users
    LEFT JOIN
        (SELECT user_id
        FROM alert_logs
        WHERE alert_logs.event_title LIKE '%blocked%'
        ) as t_alerts
            on t_alerts.user_id = users.user_id

    LEFT JOIN
        (SELECT user_id
        FROM location_logs
        ) as t_locations
            on t_locations.user_id = users.user_id

    WHERE users.user_id IN(1,2,3,4,5,6,7,8)
");

$stmt->execute();

//get results
$results = $stmt->fetch(PDO::FETCH_ASSOC);

EDIT :

A bit of a modification to eliminate the need of supplying the IN values... I use this in some other queries to only get results for 'active' users...

$stmt = $db->prepare("
    SELECT
        (SELECT COUNT(*)
        FROM alert_logs al
        WHERE event_title LIKE '%blocked%' AND al.user_id = u.user_id
        ) as alerts,
        (SELECT COUNT(*)
        FROM location_logs ll
        WHERE ll.user_id = u.user_id
        ) as locations
    FROM
        ( SELECT account_id, computer_id
            FROM computers
            WHERE account_id = :account_id
            ORDER BY computer_id ASC LIMIT 0, :licenses
        ) as c
        INNER JOIN users as u
            on u.computer_id = c.computer_id
");

$binding = array(
    'account_id' => $_SESSION['user']['account_id'],
    'licenses' => $_SESSION['user']['licenses']
);
$stmt->execute($binding);

I am running into the problem mentioned below with this statement... it is returning an array of counts per user rather than all counts combined into one result.

Array
(
    [0] => Array
        (
            [alerts] => 6
            [locations] => 4
        )

    [1] => Array
        (
            [alerts] => 3
            [locations] => 5
        )

    [2] => Array
        (
            [alerts] => 1
            [locations] => 4
        )

    [3] => Array
        (
            [alerts] => 0
            [locations] => 0
        )

    [4] => Array
        (
            [alerts] => 0
            [locations] => 0
        )

    [5] => Array
        (
            [alerts] => 0
            [locations] => 0
        )

    [6] => Array
        (
            [alerts] => 0
            [locations] => 0
        )

    [7] => Array
        (
            [alerts] => 0
            [locations] => 0
        )

)

What can I do to 'combine' results?

Answer 1

The problem is that the alerts are multiplying with the locations. So, if there are 10 alerts and 5 locations, the result is 50 rows. That is what gets counted.

The easy solution is to use count(distinct):

SELECT
    count(distinct t_alerts.user_id) as alerts,
    count(distinct t_locations.user_id) as locations
. . . 

The better solution is often to use a subquery to do the counting along each dimension, and then join the results together.

EDIT:

In your case, nested subqueries in the select might be the best approach, because the query filters on users:

SELECT (SELECT COUNT(*)
        FROM alert_logs al
        WHERE event_title LIKE '%blocked%' AND
              al.user_id = u.user_id
       ) as alerts,
       (SELECT COUNT(*)
        FROM location_logs ll
        WHERE ll.user_id = u.user_id
       ) as locations
FROM users u
WHERE u.user_id IN (1,2,3,4,5,6,7,8)

EDIT II:

I see, there is no group by at the end of your query. In that case, you might as well do:

SELECT (SELECT COUNT(*)
        FROM alert_logs al
        WHERE event_title LIKE '%blocked%' AND
              al.user_id IN (1,2,3,4,5,6,7,8)
       ) as alerts,
       (SELECT COUNT(*)
        FROM location_logs ll
        WHERE ll.user_id IN (1,2,3,4,5,6,7,8)
       ) as locations;

You don't need the users table at all.