CODEWITHSUNDEEP
mysqlsql
user993211
user993211

Reputation: 3

Multiple Count Summaries in single query

I have a Database Table of Jobs. Each Job was created by a User. There are many users. The User Table contains the user_id and the users email address.

---------------
**JOBS_TABLE**
---------------
JOB_ID
USER_ID
---------------

---------------
**USERS_TABLE**
---------------
USER_ID
USER_EMAIL
---------------

I want to know how many Jobs each user has created. Is it possible to do this in just SQL?

Ideally I would like the results like this.

------------------------------
|USER_ID|JOB_COUNT|USER_EMAIL|
------------------------------
|user1  |2000     |[email protected] |
|user2  |5433     |[email protected]|
------------------------------

This is once off report so I am not worried about performance. I am using MySQL.

Thanks

Upvotes: 0

Views: 134

Answers (3)

Manigandan Arjunan
Manigandan Arjunan

Reputation: 2265

SELECT u.user_id,u.user_email,count(j.job_id) as jobcount FROM users u join jobs j on u.user_id=j.user_id group by u.user_id

This query will output as follows:

user_id   user_email     jobcount
 1         [email protected]    3
 2         [email protected]     1

Upvotes: 0

Rahul
Rahul

Reputation: 77866

    select user.USER_ID,user.USER_EMAIL,count(distinct jobs.JOB_ID) 
from USERS_TABLE user natural join JOBS_TABLE jobs group by user.USER_ID

Upvotes: 0

Asken
Asken

Reputation: 8051

This is a standard count query (user LEFT JOIN instead of INNER JOIN if you want all even if they don't have a job record):

SELECT
  U.USER_ID,
  COUNT(JOB_ID) AS JOB_COUNT,
  U.USER_EMAIL
FROM
  USERS_TABLE U

  INNER JOIN JOBS_TABLE J ON
  U.USER_ID = J.USER_ID
GROUP BY
  U.USER_ID,
  U.USER_EMAIL

Upvotes: 2

Related Questions