8

I have 3 tables:

Players:

    mysql> SELECT * FROM players;
+-----------+---------+----------------------+----------------------+-----------------+------------------------------+-----------------------+---------------------+
| player_id | team_id | player_name          | player_jersey_number | player_position | player_email                 | player_contact_number | player_timestamp    |
+-----------+---------+----------------------+----------------------+-----------------+------------------------------+-----------------------+---------------------+
|         1 |       4 | Popoy Alfonso        |                    2 |                 | [email protected]       | 09263453234           | 2015-08-05 00:48:10 |
|         2 |       4 | Karlo Ripas          |                   10 |                 | [email protected]         | 09212354324           | 2015-08-05 00:50:03 |
|         3 |       4 | VHaughn Von          |                   32 |                 | [email protected]              | 09361234565           | 2015-08-05 00:51:00 |
|         4 |       4 | Lordie Zalbahe       |                   23 |                 | [email protected]      | 09391222334           | 2015-08-05 00:52:42 |
|         5 |       4 | Jigs Selda           |                    8 |                 | [email protected]          | 09325566323           | 2015-08-05 00:53:36 |
|         6 |       4 | Rhan Garniel         |                    3 |                 | [email protected]        | 09129503400           | 2015-08-05 00:54:20 |
|         7 |       5 | Johnritz Rodriguez   |                   11 |                 | [email protected]           | 09231112346           | 2015-08-05 00:56:02 |
|         8 |       5 | Garret Van Sarmiento |                    7 |                 | [email protected] | 09264565600           | 2015-08-05 00:56:53 |
|         9 |       5 | Lester Selda Lineses |                   12 |                 | [email protected]      | 09068746354           | 2015-08-05 00:57:47 |
|        10 |       5 | Laurence Lineses     |                   44 |                 | [email protected]    | 09847354672           | 2015-08-05 00:59:33 |
|        11 |       5 | Xandrix Buendia      |                    1 |                 | [email protected]     | 09234665590           | 2015-08-05 01:00:12 |
|        12 |       5 | Betoyskie Limpiada   |                   45 |                 | [email protected]        | 09213456667           | 2015-08-05 01:01:15 |
+-----------+---------+----------------------+----------------------+-----------------+------------------------------+-----------------------+---------------------+

Teams:

    mysql> SELECT * FROM teams;
+---------+-----------+----------------------+---------------------+
| team_id | season_id | team_name            | team_timestamp      |
+---------+-----------+----------------------+---------------------+
|       4 |         1 | Quiapo A             | 2015-08-05 00:30:13 |
|       5 |         1 | Quiapo B             | 2015-08-05 00:30:25 |
|       6 |         1 | Balik-Balik Warriors | 2015-08-05 00:31:13 |
|       7 |         1 | Adamson Falcons      | 2015-08-05 00:31:42 |
|       8 |         1 | Pasay Flooders       | 2015-08-05 00:32:04 |
|       9 |         1 | Marina Dragons       | 2015-08-05 00:32:22 |
|      10 |         1 | MDC Archers          | 2015-08-05 00:33:12 |
|      11 |         2 | Quiapo A             | 2015-08-05 00:34:25 |
|      12 |         2 | Quiapo B             | 2015-08-05 00:34:38 |
|      13 |         2 | Marikina Eagels      | 2015-08-05 00:35:11 |
|      14 |         2 | TIP Steallers        | 2015-08-05 00:35:32 |
|      15 |         2 | Gasan Blue Eagles    | 2015-08-05 00:36:12 |
+---------+-----------+----------------------+---------------------+

Seasons:

mysql> SELECT * FROM seasons;
+-----------+-------------+----------------------+---------------------+
| season_id | season_name | season_event_name    | season_timestamp    |
+-----------+-------------+----------------------+---------------------+
|         1 | Season 1    | Summer Games         | 2015-08-05 00:23:15 |
|         2 | Season 2    | Aniversary Sportfest | 2015-08-05 00:25:10 |
+-----------+-------------+----------------------+---------------------+

I'm currently working on this query but the result is not correct.

SELECT 
    teams.team_name,
    (
        SELECT COUNT(*) 
        FROM teams 
        INNER JOIN players 
            ON teams.team_id = players.team_id
    ) as num_of_players, 
    teams.team_timestamp
FROM teams 
INNER JOIN seasons 
    ON seasons.season_id = teams.season_id 
GROUP BY teams.team_name;

Output:

+----------------------+----------------+---------------------+
| team_name            | num_of_players | team_timestamp      |
+----------------------+----------------+---------------------+
| Adamson Falcons      |             12 | 2015-08-05 00:31:42 |
| Balik-Balik Warriors |             12 | 2015-08-05 00:31:13 |
| Gasan Blue Eagles    |             12 | 2015-08-05 00:36:12 |
| Marikina Eagels      |             12 | 2015-08-05 00:35:11 |
| Marina Dragons       |             12 | 2015-08-05 00:32:22 |
| MDC Archers          |             12 | 2015-08-05 00:33:12 |
| Pasay Flooders       |             12 | 2015-08-05 00:32:04 |
| Quiapo A             |             12 | 2015-08-05 00:30:13 |
| Quiapo B             |             12 | 2015-08-05 00:30:25 |
| TIP Steallers        |             12 | 2015-08-05 00:35:32 |
+----------------------+----------------+---------------------+

The result that I want is this:

+----------------------+----------------+---------------------+
| team_name            | num_of_players | team_timestamp      |
+----------------------+----------------+---------------------+
| Adamson Falcons      |              0 | 2015-08-05 00:31:42 |
| Balik-Balik Warriors |              0 | 2015-08-05 00:31:13 |
| Gasan Blue Eagles    |              0 | 2015-08-05 00:36:12 |
| Marikina Eagels      |              0 | 2015-08-05 00:35:11 |
| Marina Dragons       |              0 | 2015-08-05 00:32:22 |
| MDC Archers          |              0 | 2015-08-05 00:33:12 |
| Pasay Flooders       |              0 | 2015-08-05 00:32:04 |
| Quiapo A             |              6 | 2015-08-05 00:30:13 |
| Quiapo B             |              6 | 2015-08-05 00:30:25 |
| TIP Steallers        |              0 | 2015-08-05 00:35:32 |
+----------------------+----------------+---------------------+
1
  • 1
    What exactly do you want to count? And why isn't there a proper teams table? (Proper meaning a table where the team_id and/or the team_name is the unique/primary key). What you call "teams" is actually a "teams-seasons" table. Commented Aug 13, 2015 at 10:33

2 Answers 2

4

Completely rearranged answer.

I've added some data to the player and team tables to make the answers more general - see bottom of post for all DDL (CREATE TABLE tab_name...) and DML (INSERT INTO tab_name VALUES...) used in this answer. I also created the season table (unchanged from the OP's - i.e. your) original data.

BTW, welcome to the forum. But you should really have given us the DDL and DML. Take a look at the tour and also the "how to help us to help you" blog - both at the bottom left of the page. But, I got interested and did it myself, but you will get more people to help you if you provide DDL and DML.

After creating and loading the tables, I ran the following SQL.

SELECT t1.team_name,
       IFNULL(t2.num_players, 0) AS strength,
       t1.team_timestamp
FROM team t1
LEFT OUTER JOIN 
    (SELECT team_id, COUNT(team_id) AS num_players 
     FROM player 
     GROUP BY team_id
    ) t2
ON t1.team_id = t2.team_id
-- GROUP BY t1.team_id, t1.team_name, t1.season_id  -- **NOTE** - see discussion below
ORDER BY strength DESC, team_name ASC;  

And the result is not quite the one you want, but close.

+----------------------+----------+---------------------+
| team_name            | strength | team_timestamp      |
+----------------------+----------+---------------------+
| Quiapo B             |        7 | 2015-08-05 00:30:25 |
| Quiapo B             |        7 | 2015-08-05 00:30:25 |
| Quiapo A             |        6 | 2015-08-05 00:30:13 |
| Quiapo A             |        6 | 2015-08-05 00:30:13 |
| Adamson Falcons      |        0 | 2015-08-05 00:31:42 |
| Balik-Balik Warriors |        0 | 2015-08-05 00:31:13 |
| Gasan Blue Eagles    |        0 | 2015-08-05 00:36:12 |
| Marikina Eagels      |        0 | 2015-08-05 00:35:11 |
| Marina Dragons       |        0 | 2015-08-05 00:32:22 |
| MDC Archers          |        0 | 2015-08-05 00:33:12 |
| Pasay Flooders       |        0 | 2015-08-05 00:32:04 |
| TIP Steallers        |        0 | 2015-08-05 00:35:32 |
+----------------------+----------+---------------------+

Running oNare's query gives (!)

+-----------+----------------+---------------------+
| team_name | num_of_players | team_timestamp      |
+-----------+----------------+---------------------+
| Quiapo A  |             26 | 2015-08-05 00:30:13 |
+-----------+----------------+---------------------+

Note that 26 = 2 * (6 + 7). But, if sql_mode is set to STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION,ONLY_FULL_GROUP_BY, this query will fail with the message

ERROR 1140 (42000): Mixing of GROUP columns (MIN(),MAX(),COUNT(),...) with no GROUP columns is illegal if there is no GROUP BY clause

However, a slight modification of oNare's query gives the same results as I obtained with my first query (with sql_mode not set to ONLY_FULL_GROUP_BY).

SELECT 
team.team_name, 
COUNT(player.player_id) as num_of_players,
team.team_timestamp
FROM team 
LEFT JOIN player ON (player.team_id = team.team_id)
LEFT JOIN seasons ON (seasons.season_id = team.season_id) 
GROUP BY team.team_name, team.season_id, team.team_timestamp -- **ADDED**
ORDER BY num_of_players DESC, team.team_name ASC;

Adding the GROUP BY gives the correct answer. It is interesting to note that PostgreSQL will throw an error if the GROUP BY line is commented out. My query won't work either on PostgreSQL if I don't add a GROUP BY line immediately before the ORDER BY line.

I think as a general answer, you could accept either of our answers as correct for your question (with GROUP BY) - it's not possible to obtain your desired results without more information in the tables, but see below.

As an aside, ONLY_FULL_GROUP_BY will be the default mode for 5.7, so might as well get it right now!

I think that your table structure needs some revision. You should have players for different seasons (makes sense, people transfer). You should have the same ids for the same team over different seasons - otherwise how are you going to aggregate statistics (goals/points/matches-won/lost) over many seasons?

TABLE DDL and DML - STRUCTURE and CONTENTS.

CREATE TABLE player (player_id INT, team_id INT, player_name VARCHAR(25));
CREATE TABLE team( team_id INT, season_id INT, team_name VARCHAR(25), team_timestamp TIMESTAMP);
CREATE TABLE season (season_id INT, season_name VARCHAR(25));

INSERT INTO player VALUES (1, 4, 'Popoy Alfonso'); 
INSERT INTO player VALUES (2, 4, 'Karlo Ripas');
INSERT INTO player VALUES (3, 4, 'VHaughn Von');
INSERT INTO player VALUES (4, 4, 'Lordie Zalbahe');
INSERT INTO player VALUES (5, 4, 'Jigs Selda'          );
INSERT INTO player VALUES (6, 4, 'Rhan Garniel'        );
INSERT INTO player VALUES (7, 5, 'Johnritz Rodriguez'  );
INSERT INTO player VALUES (8, 5, 'Garret Van Sarmiento');
INSERT INTO player VALUES (9, 5, 'Lester Selda Lineses');
INSERT INTO player VALUES (10, 5,  'Laurence Lineses'    );
INSERT INTO player VALUES (11, 5,  'Xandrix Buendia'      );
INSERT INTO player VALUES (12, 5, 'Betoyskie Limpiada'   );
INSERT INTO player VALUES (12, 5, 'Donald Duck'   );  -- Added Donald Duck!

-- I inserted extra records into the player table as below.

INSERT INTO player VALUES (1, 11, 'Popoy Alfonso'); 
INSERT INTO player VALUES (2, 11, 'Karlo Ripas');
INSERT INTO player VALUES (3, 11, 'VHaughn Von');
INSERT INTO player VALUES (11, 11, 'Lordie Zalbahe');
INSERT INTO player VALUES (5, 11, 'Jigs Selda'          );
INSERT INTO player VALUES (6, 11, 'Rhan Garniel'        );
INSERT INTO player VALUES (7, 12, 'Johnritz Rodriguez'  );
INSERT INTO player VALUES (8, 12, 'Garret Van Sarmiento');
INSERT INTO player VALUES (9, 12, 'Lester Selda Lineses');
INSERT INTO player VALUES (10, 12,  'Laurence Lineses'    );
INSERT INTO player VALUES (11, 12,  'Xandrix Buendia'      );
INSERT INTO player VALUES (12, 12, 'Betoyskie Limpiada'   );

For the team table, I gave QuiradoA and QuiradoB the same id in season 1 and season 2 - it makes no sense to me to have different ids for the same team just because the season changes. I also retained the same TIMESTAMP for QuiradoA and B.

INSERT INTO team VALUES (   4 ,         1 , 'Quiapo A'             , '2015-08-05 00:30:13');
INSERT INTO team VALUES (   5 ,         1 , 'Quiapo B'             , '2015-08-05 00:30:25');
INSERT INTO team VALUES (   6 ,         1 , 'Balik-Balik Warriors' , '2015-08-05 00:31:13');
INSERT INTO team VALUES (   7 ,         1 , 'Adamson Falcons'      , '2015-08-05 00:31:42');
INSERT INTO team VALUES (   8 ,         1 , 'Pasay Flooders'       , '2015-08-05 00:32:04');
INSERT INTO team VALUES (   9 ,         1 , 'Marina Dragons'       , '2015-08-05 00:32:22');
INSERT INTO team VALUES (  10 ,         1 , 'MDC Archers'          , '2015-08-05 00:33:12');
INSERT INTO team VALUES (   4 ,         2 , 'Quiapo A'             , '2015-08-05 00:30:13'); -- **NOTE** `id` and `TIMESTAMP` for both A & B.
INSERT INTO team VALUES (   5 ,         2 , 'Quiapo B'             , '2015-08-05 00:30:25');
INSERT INTO team VALUES (  13 ,         2 , 'Marikina Eagels'      , '2015-08-05 00:35:11');
INSERT INTO team VALUES (  14 ,         2 , 'TIP Steallers'        , '2015-08-05 00:35:32');
INSERT INTO team VALUES (  15 ,         2 , 'Gasan Blue Eagles'    , '2015-08-05 00:36:12');



INSERT INTO season VALUES(1, 'Season 1');
INSERT INTO season VALUES(2, 'Season 2');
2
  • 1
    "You shouldn't use plural names for tables"....a few paragraphs later "I was interested in onares different approach, so I created a seasons table". Make up your mind do you recommend the plural table names or not?
    – JonH
    Commented Aug 13, 2015 at 2:57
  • 1
    @JonH - I was having enough trouble running queries on different servers and I wanted to have to modify oNane's queries as little as possible, so I used the plural. I would not normally do so. Apologies if you were discommoded.
    – Vérace
    Commented Aug 13, 2015 at 3:12
1

Your query is giving you 12 num_of_players because your counting just the subquery returned rows, if you run SELECT COUNT(*) FROM teams INNER JOIN players ON teams.team_id = players.team_id; you will see what you're really doing.

To fix your syntax just one more LEFT JOIN:

SELECT 
    teams.team_name, 
    COUNT(players.player_id) as num_of_players, 
    teams.team_timestamp
FROM test.teams 
LEFT JOIN test.players ON (players.team_id=teams.team_id)
LEFT JOIN test.seasons ON (seasons.season_id = teams.season_id) 
GROUP BY teams.team_name;

Test info:

mysql> SELECT * FROM test.players;
+-----------+---------+--------------------+----------------------+-----------------+--------------+-----------------------+------------------+
| player_id | team_id | player_name        | player_jersey_number | player_position | player_email | player_contact_number | player_timestamp |
+-----------+---------+--------------------+----------------------+-----------------+--------------+-----------------------+------------------+
|         1 | 4       | Popoy Alfonso      | 2                    | NULL            | NULL         | NULL                  | NULL             |
|         2 | 4       | NULL               | 10                   | NULL            | NULL         | NULL                  | NULL             |
|         3 | 4       | NULL               | NULL                 | NULL            | NULL         | NULL                  | NULL             |
|         4 | 4       | NULL               | NULL                 | NULL            | NULL         | NULL                  | NULL             |
|         5 | 4       | NULL               | NULL                 | NULL            | NULL         | NULL                  | NULL             |
|         6 | 4       | NULL               | NULL                 | NULL            | NULL         | NULL                  | NULL             |
|         7 | 5       | Johnritz Rodriguez | 11                   | NULL            | NULL         | NULL                  | NULL             |
|         8 | 5       | NULL               | NULL                 | NULL            | NULL         | NULL                  | NULL             |
|         9 | 5       | NULL               | NULL                 | NULL            | NULL         | NULL                  | NULL             |
|        10 | 5       | NULL               | NULL                 | NULL            | NULL         | NULL                  | NULL             |
|        11 | 5       | NULL               | NULL                 | NULL            | NULL         | NULL                  | NULL             |
|        12 | 5       | NULL               | NULL                 | NULL            | NULL         | NULL                  | NULL             |
+-----------+---------+--------------------+----------------------+-----------------+--------------+-----------------------+------------------+
12 rows in set (0.00 sec)

mysql> SELECT * FROM test.teams;
+---------+-----------+----------------------+----------------+
| team_id | season_id | team_name            | team_timestamp |
+---------+-----------+----------------------+----------------+
|       4 | 1         | Quiapo A             | NULL           |
|       5 | 1         | Quiapo B             | NULL           |
|       6 | 1         | Balik-Balik Warriors | NULL           |
|       7 | 1         | Adamson Falcons      | NULL           |
|       8 | 1         | Pasay Flooders       | NULL           |
|       9 | 1         | Marina Dragons       | NULL           |
|      10 | 1         | MDC Archers          | NULL           |
|      11 | 2         | Quiapo A             | NULL           |
|      12 | 2         | Quiapo B             | NULL           |
|      13 | 2         | Marikina Eagels      | NULL           |
|      14 | 2         | TIP Steallers        | NULL           |
|      15 | 2         | Gasan Blue Eagles    | NULL           |
+---------+-----------+----------------------+----------------+
12 rows in set (0.00 sec)

mysql> SELECT * FROM test.seasons;
+-----------+-------------+----------------------+------------------+
| season_id | season_name | season_event_name    | season_timestamp |
+-----------+-------------+----------------------+------------------+
|         1 | Season 1    | Summer Games         | NULL             |
|         2 | Season 2    | Aniversary Sportfest | NULL             |
+-----------+-------------+----------------------+------------------+
2 rows in set (0.00 sec)

mysql> 

Your QUERY:

mysql> SELECT teams.team_name, (SELECT COUNT(*) FROM teams INNER JOIN players ON teams.team_id = players.team_id) as num_of_players, teams.team_timestamp
    -> FROM teams 
    -> INNER JOIN seasons 
    -> ON seasons.season_id = teams.season_id 
    -> GROUP BY teams.team_name;
+----------------------+----------------+----------------+
| team_name            | num_of_players | team_timestamp |
+----------------------+----------------+----------------+
| Adamson Falcons      |             12 | NULL           |
| Balik-Balik Warriors |             12 | NULL           |
| Gasan Blue Eagles    |             12 | NULL           |
| Marikina Eagels      |             12 | NULL           |
| Marina Dragons       |             12 | NULL           |
| MDC Archers          |             12 | NULL           |
| Pasay Flooders       |             12 | NULL           |
| Quiapo A             |             12 | NULL           |
| Quiapo B             |             12 | NULL           |
| TIP Steallers        |             12 | NULL           |
+----------------------+----------------+----------------+
10 rows in set (0.00 sec)

mysql> 

Fixed QUERY:

mysql> SELECT 
    ->     teams.team_name, 
    ->     COUNT(players.player_id) as num_of_players, 
    ->     teams.team_timestamp
    -> FROM test.teams 
    -> LEFT JOIN test.players ON (players.team_id=teams.team_id)
    -> LEFT JOIN test.seasons ON (seasons.season_id = teams.season_id) 
    -> GROUP BY teams.team_name;
+----------------------+----------------+----------------+
| team_name            | num_of_players | team_timestamp |
+----------------------+----------------+----------------+
| Adamson Falcons      |              0 | NULL           |
| Balik-Balik Warriors |              0 | NULL           |
| Gasan Blue Eagles    |              0 | NULL           |
| Marikina Eagels      |              0 | NULL           |
| Marina Dragons       |              0 | NULL           |
| MDC Archers          |              0 | NULL           |
| Pasay Flooders       |              0 | NULL           |
| Quiapo A             |              6 | NULL           |
| Quiapo B             |              6 | NULL           |
| TIP Steallers        |              0 | NULL           |
+----------------------+----------------+----------------+
10 rows in set (0.00 sec)

mysql> 

See the output here: SQLFiddle

1

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.