Selecting Counts from Different Tables with a Subquery

Question

I'm new to MySQL, and I'd like some help in setting up a MySQL query to pull some data from a few tables (~100,000 rows) in a particular output format.

This problem involves three SQL tables:

allusers : This one contains user information. The columns of interest are userid and vip

table1 and table2 contain data, but they also have a userid column, which matches the userid column in allusers.

What I'd like to do:

I'd like to create a query which searches through allusers, finds the userid of those that are VIP, and then count the number of records in each of table1 and table2 grouped by the userid. So, my desired output is:

  userid  | Count in Table1  | Count in Table2
    1     |        5         |         21
    5     |        16        |         31
    8     |        21        |         12

What I've done so far:

I've created this statement:

SELECT userid, count(1) 
FROM table1 
WHERE userid IN  (SELECT userid FROM allusers WHERE vip IS NOT NULL)
GROUP BY userid

This gets me close to what I want. But now, I want to add another column with the respective counts from table2

I also tried using joins like this:

select A.userid, count(T1.userid), count(T2.userid) from allusers A
left join table1 T1 on T1.userid = A.userid
left join table2 T2 on T2.userid = A.userid
where A.vip is not null
group by A.userid

However, this query took a very long time and I had to kill the query. I'm assuming this is because using Joins for such large tables is very inefficient.

Similar Questions

This one is looking for a similar result as I am, but doesn't need nearly as much filtering with subqueries

This one sums up the counts across tables, while I need the counts separated into columns

Could someone help me set up the query to generate the data I need?

Thanks!

score 2 · Answer 1 · answered Sep 03 '20 at 15:12

You need to pre-aggregate first, then join, otherwise the results will not be what you expect if a user has several rows in both table1 and table2. Besides, pre-aggregation is usually more efficient than outer aggregation in a situation such as yours.

Consider:

select a.userid, t1.cnt cnt1, t2.cnt cnt2
from allusers a
left join (select userid, count(*) cnt from table1 group by userid) t1
    on t1.userid = a.userid
left join (select userid, count(*) cnt from table2 group by userid) t2
    on t2.userid = a.userid
where a.vip is not null

I never considered pre-aggregating first. But that makes a lot more sense. This query worked perfectly. Thank you. — Aommaster, Sep 03 '20 at 15:27

score 1 · Accepted Answer · answered Sep 03 '20 at 15:35

This is a case where I would recommend correlated subqueries:

select a.userid, 
       (select count(*) from table1 t1 where t1.userid = a.userid) as cnt1,
       (select count(*) from table2 t2 where t2.userid = a.userid) as cnt2
from allusers a
where a.vip is not null;

The reason that I recommend this approach is because you are filtering the alllusers table. That means that the pre-aggregation approach may be doing additional, unnecessary work.

Selecting Counts from Different Tables with a Subquery

What I'd like to do:

What I've done so far:

Similar Questions

2 Answers2