Multiple Count Summaries in single query

Question

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

Asken · Accepted Answer · 2011-12-02 09:40:14Z

2

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

answered Dec 2, 2011 at 9:40

Asken

8,14711 gold badges50 silver badges84 bronze badges

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

Comments

Manigandan Arjunan · Accepted Answer · 2011-12-02 09:55:54Z

0

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

edited Dec 2, 2011 at 9:55

answered Dec 2, 2011 at 9:51

Manigandan Arjunan

2,2651 gold badge25 silver badges43 bronze badges

Comments

Rahul · Accepted Answer · 2011-12-02 09:43:51Z

0

    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

answered Dec 2, 2011 at 9:43

Rahul

78.1k14 gold badges80 silver badges132 bronze badges

Collectives™ on Stack Overflow

Multiple Count Summaries in single query

3 Answers 3

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related