CODEWITHSUNDEEP
mysqlsql
Sasha
Sasha

Reputation: 8705

MySQL - join two tables, group and count

I have two tables:

reviewStatusPhases - id|name

and 

userPhase - id|reviewStatusPhase_id|user_id|created_at|updated_at

The reviewStatusPhases table have records inserted (Active, Inactive, On Pause, Terminated...), and userPhase is empty.

The tables are connected via

 userPhase.reviewStatusPhase_id = reviewStatusPhases.id

one to one.

Is it possible that in one query I get all reviewStatusPhases, and cound how many users are in each phase? In this case I will get something like this:

Active (0 Users)
Inactive (0 Users)
On Pause (0 Users)
Terminated (0 Users)

Upvotes: 0

Views: 2338

Answers (5)

Muhammad Azeem
Muhammad Azeem

Reputation: 1159

SELECT 
DISTINCT s.s_level AS 'Level', 
COUNT(DISTINCT s.s_id) AS Schools, 
COUNT(DISTINCT st.st_id) AS Teachers 
FROM schools AS s
JOIN school_teachers AS st ON st.st_school_idFk = s.s_id AND st.st_status = 1
WHERE s.s_status = 1 
GROUP BY s.s_level

Upvotes: 0

Shyam Vemula
Shyam Vemula

Reputation: 589

I hope the following will be helpful 

SELECT RPS.Name,  COUNT(UP.user_id) 
FROM reviewStatusPhases RPS
LEFT OUTER JOIN userphases UP ON RPS.id = UP.reviewStatusPhase_id
GROUP BY RPS.Name
ORDER BY RPS.Name

Upvotes: 1

Deepansh Sachdeva
Deepansh Sachdeva

Reputation: 1198

Query will be as follows:

SELECT r.name as `name`, count(u.id) as `count` FROM reviewStatusPhases r LEFT OUTER JOIN userPhase u ON r.id = u.reviewStatusPhase_id GROUP BY r.name
  1. left outer join with reviewStatusPhases on left to show all names.
  2. group by names of reviewStatusPhases.
  3. display reviewStatusPhases name and count of user id's (to neglect null values)

Upvotes: 2

AT-2017
AT-2017

Reputation: 3149

Use LEFT JOIN as follows:

SELECT COUNT(m.UserId) FROM Table1 m 
LEFT JOIN Table2 k ON k.StatusId = m.StatusId
WHERE k.Status = 'Inactive'

You can easily use the Status column to track the users and their activities. In your case, ReviewStatus.

Upvotes: 1

EdmCoff
EdmCoff

Reputation: 3576

I'm making some assumptions here (e.g. INNER JOIN versus LEFT JOIN in the join, and DISTINCT in the count), but it sounds like you just want

SELECT reviewStatusPhases.name, COUNT(DISTINCT userPhase.user_id)
FROM userPhase INNER JOIN reviewStatusPhases
   ON userPhase.reviewStatusPhase_id = reviewStatusPhases.id
GROUP BY reviewStatusPhases.name

Upvotes: 4

Related Questions