[RESOLVED] join query and count and sum

[jansky]

greetings

how do i generate total numbers using COUNT() and summary using SUM()
with a join statement

im getting empty result

the sql below is a basic things like what my current SQL do now

select table1.name, table1.lastname, table1.numberofgames,
DATEDIFF(CURDATE(),table1.last_game) AS Lastgameday 
from table1
Having lastgameday<10

now this display the output needed

but then there is a new revision which I will have to join the join also has subqueries and count and other query computation

select table1.name, table1.last, count(table2.numberofgames) as numberofgames, sum(table2.shoot)as  totalshoot, sum(table2.shoot)/count(table2.numberofgames) as perg,
DATEDIFF(CURDATE(),table1.last_game) AS Lastgameday 
from table1
left join (select * from table2 where where DATEDIFF(CURDATE(),table2.last_seen_online))table2 on (table1.id = table2.teamid)


Having lastgameday<10

why this query return 0 ?
TIA

[jansky]

Between this is the full queries

SELECT SQL_CALC_FOUND_ROWS  

	player_profile.name AS Name, 


count(players_team.id) as cmember,
sum(players_team.score) as totalscore,

player_profile.last_updated AS LastUpdated,
DATEDIFF(CURDATE(),player_profile.last_updated) AS Last30Days 
FROM player_profile 
inner  JOIN (SELECT * FROM players_team where DATEDIFF(CURDATE(),players_team.last_seen_online)<=30 ) players_team ON
	(players_team.guild_id= player_profile.game_id) 
	WHERE (player_profile.members > 0) 
	HAVING DATEDIFF(CURDATE(),player_profile.last_updated) <= 30 

This only rretun 1 row instead of 3 rows
???

[Weedpacket]

You'd need a GROUP BY clause between your WHERE clause and your HAVING clause saying what columns you want to group the records by.