CODEWITHSUNDEEP
mysqldatabasejoincount
ThriceGood
ThriceGood

Reputation: 1703

MySQL COUNT on multiple relations

I am trying to figure out how to return the counts of multiple different items in seperate tables related to the table i am joining too.

Im quite new to joins so im not sure if im using correct join. hopefully you can help me!

the tables would be like this:

staff_type table

id        type
1         doctor
2         nurse
3         surgeon

staff table

id        type_id        name
1         1              bob
2         1              jane
3         2              phil
4         2              esther
5         3              michael jackson

im tring to construct a statement that will return me the COUNT off the various different staff types, as in how many dactors, how many nurses etc. I also want the query to bring the data from the staff_type table.

I haven't much ideas on how to construct this query, but it may look something like this:

SELECT staff_type.*, COUNT(Staff.type_id = staff_type.id)
INNER JOIN staff AS Staff ON (staff_type.id = Staff.type_id)

i know this is nothing like what its supposed to be, bit hopefully some of you can point me in the right direction. Other posts on this topic are hard for me to understand and look like they are trying to do something slightly different.

thanks for any help!

Upvotes: 3

Views: 2099

Answers (2)

spencer7593
spencer7593

Reputation: 108370

You can use something like this as an example:

SELECT t.id
     , t.name
     , COUNT(s.id) AS count_staff
  FROM staff_type t
  LEFT
  JOIN staff s
    ON s.type_id = t.id
 GROUP
    BY t.id
     , t.name

To understand what that's doing, you can remove the GROUP BY and the aggregate expression (COUNT) function in the SELECT list, and see the rows returned by the JOIN operation.

For example:

SELECT t.id      AS `t.id`
     , t.name    AS `t.name`
     , s.id      AS `s.id`
     , s.name    AS `s.name`
     , s.type_id AS `s.type_id`
  FROM staff_type t
  LEFT
  JOIN staff s
    ON s.type_id = t.id
 ORDER
    BY t.id
     , s.id

Note that the LEFT keyword indicates an "outer join". This is going to return all the rows from the table on the left side, even if there aren't any matching rows on the right side. (This will let us get a "zero" count for a staff_type that doesn't have any related staff.)

When we add the GROUP BY clause, that says to "collapse" all the rows that have the same values for the expressions or columns in the GROUP BY list.

We can use an aggregate function, such as COUNT(), SUM(), MAX(), MIN() to perform an operation on all of the rows that are collapsed into a group.

The COUNT() aggregate starts at zero, and increments by one for every non-NULL value. So, we use an expression, COUNT(s.id) that we are guaranteed will be non-NULL if there is a matching row from staff, and will be NULL if there isn't a matching row.

(I hope this helps clear up some of your confusion.)

Upvotes: 2

senK
senK

Reputation: 2802

The query will be,

select staff_type.*, count(staff.id) as count  from staff_type left join staff on (staff.type_id = staff_type.id) group by staff_type.id

Upvotes: 0

Related Questions