MYSQL - get count of rows from 2 tables

Question

I hope this is a simple question for someone, I'm trying to get a count of users that belong to a certain regionID.

I have a users table and a venues table and I want to match the users region and the venues region for a particular regionID.

eg

SELECT COUNT(*) FROM users 
LEFT JOIN venues 
ON users.region = venues.region 
WHERE  venues.regionID = '18'

My results from various attempts result in millions, to 1 to non responsive page (gulp)

Example table data:

users

userID = 1  
region = 'Brisbane'

venues

venueID = 1  
region = 'Brisbane'  
regionID = '18'// which equates to Queensland

thanks

You'll either need to describe your goal a bit more clearly, or show us a piece of the original dataset and what your desired query would ultimately return. As of right now, it's not very clear what you're trying to accomplish. — Tyler Roper
– Tyler Roper, Commented Feb 21, 2017 at 20:25
So, does the query you have shown work? What does it output? Why are you using LEFT JOIN? What does users.region contain, does it match with users.region? — gen_Eric
– gen_Eric, Commented Feb 21, 2017 at 20:27
hmmm I think the question is this statement "count of users that belong to certain regionID", the rest is well unclear — niceman
– niceman, Commented Feb 21, 2017 at 20:31
Hi thanks for the feedback, My goal is just to get the number of users for a particular regionID, and display this number on the page. An example users.region = 'Brisbane' and venues.region = 'Brisbane' and venues.regionID = '18'. 18 will be the number for the state Queensland. So my result should show me a count of all users that belong to the state queensland. — Daryl Fincham
– Daryl Fincham, Commented Feb 21, 2017 at 20:37

O. Jones · Accepted Answer · 2017-02-21 21:33:21Z

1

I guess (without knowing) you want a count of distinct users matching your regionId criterion.

You could do this.

SELECT COUNT(*)
  FROM users
 WHERE region IN (SELECT region FROM venues WHERE regionID = '18')

Or, you could get the number of distinct users in each region like this.

SELECT COUNT(DISTINCT users.userID) users,
       region.regionID
  FROM users
  JOIN venues ON users.region = venues.region
 GROUP BY region.regionID

I guess you have a userID column in your users table.

COUNT(*) probably won't work in a JOIN, because the result of the join operation probably contains a different number of rows than the users table itself.

This is one of those operations where it helps to know lots of SQL even to specify the result you want. Fortunately the DISTINCT keyword can help reduce large counts to smaller, and more meaningful counts.

To help with performance, you probably should add a compound index on your users table on the (region, usersID) columns. This DDL query may do that for you.

      ALTER TABLE users ADD INDEX region_userID  (region, usersID)

Also, on your other table, try these two

      ALTER TABLE venues ADD INDEX regionID_region (regionID, region)
      ALTER TABLE venues ADD INDEX region_regionID (region, regionID)

Read this: http://use-the-index-luke/

edited Feb 21, 2017 at 21:33

answered Feb 21, 2017 at 20:47

O. Jones

110k17 gold badges134 silver badges187 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Daryl Fincham Over a year ago

thanks for the help. Yes there is a usersID on the users table. yeah - count of distinct users matching your regionId criterion. That's right. I will use one of these select statements for each regionID I need to display a count for. My users table as 120,000 rows and the venues 3,000. Trying the first snippet of code seem to timeout the page. I'll try the other code snippet out too. Thanks

Collectives™ on Stack Overflow

MYSQL - get count of rows from 2 tables

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related