How can I join 2 tables together, and then count by 2 columns?

Question

Say I have these 2 tables:

What is the best way for me to join these 2 tables, and then count the number of userids per country, per category?

Answer 1

You could try the below query, I don't have mySQL, but it is standard SQL that should do the work.

SELECT country, category, count(1) FROM users u 
   JOIN submissions s ON u.userid= s.userid GROUP BY country, category

Answer 2

You can do the following (all of the code below is available on the fiddle here):

CREATE TABLE users
(
  user_id INTEGER NOT NULL PRIMARY KEY,
  u_date  DATE NOT NULL,
  country VARCHAR (100) NOT NULL
);

Populate it (some extra records for demonstration purposes):

INSERT INTO users VALUES
(123, '2022-12-01', 'USA'),
(124, '2022-12-01', 'Ireland'),
(125, '2022-12-01', 'Japan');

Note the use of the ISO 8601 date format (i.e. YYYY-MM-DD) - this is a standard and most database systems accept dates in this format.

And:

CREATE TABLE submissions
(
  submission_id   INTEGER PRIMARY KEY,
  user_id         INTEGER NOT NULL,
  submission_date DATE,
  category        VARCHAR (100) NOT NULL,
  CONSTRAINT sub_user_fk FOREIGN KEY (user_id) REFERENCES users (user_id)
);

And again with some extra records:

INSERT INTO submissions VALUES
(1, 123, '2022-12-02', 'business'),
(2, 123, '2022-12-02', 'business'),
(3, 123, '2022-12-02', 'tourism'),
(4, 123, '2022-12-02', 'tourism'),
(5, 123, '2022-12-02', 'tourism'),
(6, 124, '2022-12-02', 'business'),
(7, 124, '2022-12-02', 'tourism');

--
-- Note: no records in submissions for user 125
--

And then we run:

SELECT 
  u.user_id, u.country, s.category, 
  COUNT(s.category) AS cat_cnt
FROM
  users u
INNER JOIN
  submissions s
  ON u.user_id = s.user_id
GROUP BY u.user_id, u.country, s.category
ORDER BY u.user_id, u.country;

Result:

user_id  country    category    cat_cnt
123          USA    tourism           3
123          USA    business          2
124      Ireland    tourism           1
124      Ireland    business          1

Note that the JOIN here is an INNER join - which is the default if not specified. However, we might well wish to know who's not made any submissions (or purchases or whatever you're interested in).

We do this by way of a LEFT JOIN which will include all of the records from the left hand side of the join with NULLs for those records on the right hand side with no corresponding entries - in this case, the user from Japan has no submissions so will not appear in the first report but does appear in the second as follows:

SELECT 
  u.user_id, u.country, COALESCE(s.category, '------'), 
  COUNT(s.category) AS cat_cnt
FROM
  users u
LEFT JOIN
  submissions s
  ON u.user_id = s.user_id
GROUP BY u.user_id, u.country, s.category
ORDER BY u.user_id, u.country;

Result:

user_id  country    coalesce    cat_cnt
123          USA     tourism          3
123          USA    business          2
124      Ireland     tourism          1
124      Ireland    business          1
125        Japan     ------           0

We use the COALESCE() function to input a string to replace NULL - just a presentation thing - you may wish to let your application's presentation layer take care of that!

As mentioned in my comment, please always include your version of MySQL! Also, when asking questions like this in future, please include a fiddle with your table definitions and some sample data so that a) we have a single source of truth for the question and that b) there is no duplication of effort on behalf of those trying to answer - help us to help you!

Edit:

As pointed out by @AnandSowmithiran, I included a bit too much detail in my original answer - I've left the original unchanged, but as he points out, the exact answer to your question is more properly formulated as follows (new fiddle added here):

First, we can add some records to the users and submissions tables:

INSERT INTO users VALUES
(126, '2022-12-01', 'USA');

And:

INSERT INTO submissions VALUES
( 8, 126, '2022-12-02',  'tourism'),
( 9, 126, '2022-12-02',  'tourism'),
(10, 126, '2022-12-02',  'tourism'),
(11, 126, '2022-12-02', 'business'),
(12, 126, '2022-12-02', 'business');

and then:

SELECT 
  u.country, COALESCE(s.category, '------') AS category, 
  COUNT(s.category) AS cat_cnt
FROM
  users u
LEFT JOIN
  submissions s
  ON u.user_id = s.user_id
GROUP BY u.country, s.category
ORDER BY u.country, category DESC, s.category;

Result:

country     category    cat_cnt
Ireland      tourism          1
Ireland     business          1
Japan        ------           0
USA          tourism          6
USA         business          4

i.e. you don't want a breakdown by user_id, just by country and category. Inspection shows that this is the correct result.