CODEWITHSUNDEEP
mysql
TC Conway
TC Conway

Reputation: 25

How can I get a total count of items?

I'm trying to figure out a MYSQL string and my noob-ness is getting in my way. I'm trying to count the total number of teams per phase.

Tables to consider:

phases
+----+------------+
| id | phase_name |
+----+------------+
|  1 | start      |
|  2 | middle     |
|  3 | end        |
|  4 | finish     |
+----+------------+

teams
+----+-----------+----------+
| id | team_name | phase_id |
+----+-----------+----------+
|  1 | team1     |        2 |
|  2 | team2     |        3 |
|  3 | team3     |        3 |
|  4 | team4     |        4 |
|  4 | team5     |        3 |
+----+-----------+----------+


Desired result
+----------+------------+-----------+
| phase_id | phase_name | tot_teams |
+----------+------------+-----------+
|        1 | start      | NULL      |
|        2 | middle     | 1         |
|        3 | end        | 3         |
|        4 | finish     | 1         |
+----------+------------+-----------+

I've tried:

SELECT
    T.phase_id, P.phase_name, COUNT(*) AS tot_teams
FROM
    teams T
LEFT JOIN
    phases P ON P.id = T.phase_id
GROUP BY
    phase_id;

but that only shows the affected phase_id's...and I'm hoping to get ALL phase_id's in a table. I also tried:

SELECT
    P.phase_name, T.phase_id, COUNT(*)
FROM
    teams T
RIGHT JOIN
    phases P on P.`id` = T.`phase_id`
GROUP BY
    P.id

but that shows invalid data. (For example, phase_id has a qty of 1 but doesn't show up in the teams table.

Can you point me in the right direction?

Thanks!

Upvotes: 0

Views: 22

Answers (1)

Barmar
Barmar

Reputation: 781004

The RIGHT JOIN is correct, but you need to use COUNT(T.phase_id) instead of COUNT(*). Otherwise, you're counting the row containing NULL that's generated for the phase with no teams.

Most people prefer to use LEFT JOIN, putting the master table first.

SELECT P.phase_name, P.phase_name, COUNT(T.phase_id)
FROM phase AS P
LEFT JOIN teams AS T ON P.id = T.phase_id
GROUP BY P.id

Upvotes: 1

Related Questions