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?

Gordon Linoff · Accepted Answer · 2014-06-04 02:20:14Z

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.

I thought of distinct, but I think that would be pretty taxing performance-wise. Can you explain a little more on the other method or point me in the right direction?
hmm... I am getting the counts, but only for one user and not all the users as a whole.
Works... I am now trying to eliminate the need for the in clauses since I am using prepared statements and binding... I have a method to do this that I use in other statements which I have included above, but am running into the 'results for each user' rather than all combined. Really appreciate the assistance with this...

Collectives™ on Stack Overflow

getting row counts from multiple tables

1 Answer 1

3 Comments

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Related