CODEWITHSUNDEEP
mysqlsqljoincountunion
user934902
user934902

Reputation: 1204

SQL Joining rows after using count

I am trying to display a list of players from team #1 and the stats to go with them, here are the tables

table 1: players

╔═══════════════════════════╗
║ id | fname | lname | team ║
╠═══════════════════════════╣
║ 1 | Jason | McFee | 1     ║
║ 2 | John | Smith  | 1     ║
║ 3 | Jack | Doe    | 1     ║
║ 4 | Wayne | Gretzky | 2   ║
╚═══════════════════════════╝

table 2: events_goals

╔═════════════════════════╗
║ id  g_id  a1_id  a2_id  ║
╠═════════════════════════╣
║ 1   1  2  3             ║
║ 2   3  1  2             ║
║ 3   2  1  NULL          ║
╚═════════════════════════╝

What I want to get is this
Name - being concat from table
Goals - COUNT all the times player id is in g_id column
Assists - COUNT (a1_id) + COUNT (a2_id) all the times playerid is in either of those columns
POINTS - sum of goals + assists

╔══════════════════════════════════════╗
║ id | name | goals | assists | points ║
╠══════════════════════════════════════╣
║ 1   J.McFee    1      2          3   ║
║ 2   J.Smith    1      2          3   ║
║ 3   J.Doe      1      1          2   ║
╚══════════════════════════════════════╝

What I have tried to do

>SELECT id,
>CONCAT_WS(', 'SUBSTR(fname, 1, 1), lname) name,
>FROM players 
>WHERE teamid = 1

This gets me a return of the names of all the players on the team with the ID of 1 with there names in proper format no problem.

I can get the count of a single player by using

>SELECT COUNT(g_id) FROM events_goals WHERE id = (playerid)

This returns the correct number of goals for player

However when I go to put it all together the stats are wrong and it only displays 1 row when i know there is supposed to be three

> SELECT a.id,
> CONCAT_WS(', 'SUBSTR(a.fname, 1, 1), a.lname) name,
> (COUNT(b.g_id))goals, 
> (COUNT(c.a1_id))a1, 
> (COUNT(d.a2_id))a2 
> FROM players a
> LEFT JOIN events_goals b ON a.id = b.g_id 
> LEFT JOIN events_goals c ON a.id = c.a1_id 
> LEFT JOIN events_goals d ON a.id = d.a2_id WHERE teamid = 1

Upvotes: 2

Views: 121

Answers (3)

Mosty Mostacho
Mosty Mostacho

Reputation: 43434

This is the query you're looking for:

SELECT
  p.id,
  CONCAT_WS(', ', SUBSTR(p.fname, 1, 1), p.lname) name,
  COALESCE(eg_goals.goals, 0) goals, 
  COALESCE(eg_assists1.assists, 0) + COALESCE(eg_assists2.assists, 0) assists,
  COALESCE(eg_goals.goals, 0) + COALESCE(eg_assists1.assists, 0) + COALESCE(eg_assists2.assists, 0) points
FROM players p
LEFT JOIN (
  SELECT g_id, COUNT(g_id) goals FROM events_goals
  GROUP BY g_id
) eg_goals ON p.id = eg_goals.g_id
LEFT JOIN (
  SELECT a1_id, COUNT(a1_id) assists FROM events_goals
  GROUP BY a1_id
) eg_assists1 ON p.id = eg_assists1.a1_id
LEFT JOIN (
  SELECT a2_id, COUNT(a2_id) assists FROM events_goals
  GROUP BY a2_id
) eg_assists2 ON p.id = eg_assists2.a2_id
WHERE p.team = 1

You should seriously reconsider redesigning your schema. Having those "events" mixed in the same table lead to horrible and very hard to maintain queries.

Upvotes: 1

Digital Alchemist
Digital Alchemist

Reputation: 2332

Using COALESCE

SELECT a.id,
        CONCAT_WS(', ', SUBSTR(a.fname, 1, 1), a.lname) name,
        (COUNT(b.g_id)) goals,  
        ((COALESCE(COUNT(c.a1_id),0)) + (COALESCE(COUNT(c.a2_id),0))) assists, 
        (COUNT(COALESCE(c.a1_id,0)) + COUNT(COALESCE(c.a2_id,0)) + COUNT(COALESCE(b.g_id,0))) points 
        FROM players a
        LEFT JOIN events_goals b ON a.id = b.g_id 
        LEFT JOIN events_goals c ON a.id = c.a1_id 
        LEFT JOIN events_goals d ON a.id = d.a2_id 
        WHERE a.teamid = 1
        GROUP BY a.id,
        CONCAT_WS(', ', SUBSTR(a.fname, 1, 1), a.lname)

Upvotes: 0

Adriaan Stander
Adriaan Stander

Reputation: 166396

You need to add a GROUP BY (see also SQL GROUP BY Statement)

The GROUP BY statement is used in conjunction with the aggregate functions to group the result-set by one or more columns.

**SQL GROUP BY Syntax**

SELECT column_name, aggregate_function(column_name)
FROM table_name
WHERE column_name operator value
GROUP BY column_name;



SELECT a.id,
CONCAT_WS(', 'SUBSTR(a.fname, 1, 1), a.lname) name,
(COUNT(b.g_id))goals, 
(COUNT(c.a1_id))a1, 
(COUNT(d.a2_id))a2 
FROM players a
LEFT JOIN events_goals b ON a.id = b.g_id 
LEFT JOIN events_goals c ON a.id = c.a1_id 
LEFT JOIN events_goals d ON a.id = d.a2_id 
WHERE teamid = 1
GROUP BY a.id,
CONCAT_WS(', 'SUBSTR(a.fname, 1, 1), a.lname)

Upvotes: 0

Related Questions