MySQL Join with optional select value

Question

I have a query on a Left join, where the Table who joins can contain zero rows. The problem is that if I select from that, then there are no rows where the join table contains zero rows. How can I select it then anyway?

Here is my query

SELECT server.id
    , COALESCE( AVG( playerData.player ) , 0 ) AS average 
FROM server 
LEFT JOIN playerData ON server.id = playerData.serverID 
    AND (playerData.timestamp > UNIX_TIMESTAMP( ) -10000)

The playerData table stores in one row how many people were on a server on a specific time. And multiple rows of this needs to be calculated to an average and this needs to be joined to the other query. When I omit the secound select column it gives me all rows (like it should), otherwise it only shows a result where also appropriate rows in the playerData table exists.

Additional Table data: playerData Table:

playerData table

For the other table only the id column is important in this case.

Your description is not clear. Please consider providing some sample data and the desired output. — PM 77-1
– PM 77-1, Commented May 22, 2013 at 20:11
Please note that you are mixing non-aggregated and aggregated columns in a query without GROUP BY. — PM 77-1
– PM 77-1, Commented May 22, 2013 at 20:15

Gordon Linoff · Accepted Answer · 2013-05-22 20:12:00Z

2

Try this:

SELECT server.id
    , COALESCE( AVG( playerData.player ) , 0 ) AS average 
FROM server 
LEFT JOIN playerData ON server.id = playerData.serverID 
    AND (playerData.timestamp > UNIX_TIMESTAMP( ) -10000)
group by server.id

Your query is missing the group by clause. When you include an aggregation function (like AVG()), the query automatically becomes an aggregation query. Without group by, all rows are aggregated into a single row. Presumably, you want one row per server.id.

answered May 22, 2013 at 20:12

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Sapd Over a year ago

Thanks, that was the problem. (But I try to add additional Information to my Question anyways as suggested in the comments, for people who have the same problem)

Rahil Wazir Over a year ago

It make sense now. If I use aggregate function in sub-query will it still consider aggregation query for outer one?

Collectives™ on Stack Overflow

MySQL Join with optional select value

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related