MyQuery optimalizations. Thanks

[Kavvson]

Can anyone make it shorter and more flexible?

$guildMembers = $SQL->query( 'SELECT COUNT(`gr`.`id`) AS `total` FROM `players` AS `p` LEFT JOIN `guild_ranks` AS `gr` ON `gr`.`id` = `p`.`rank_id` WHERE `gr`.`guild_id` = '.$guild_id )->fetch( );        $allM = $SQL->query ('SELECT COUNT(1) as `people` FROM `players` WHERE `rank_id` IN (SELECT `id` FROM `guild_ranks` WHERE `guild_id` = '.$guild_id.') AND online = 1')->fetch(); 
		$allM1 = $SQL->query ('SELECT SUM(`level`) as `level` FROM `players` WHERE `rank_id` IN (SELECT `id` FROM `guild_ranks` WHERE `guild_id` = '.$guild_id.') ')->fetch(); 
		$allM2 = $SQL->query ('SELECT AVG(`level`) as `level` FROM `players` WHERE `rank_id` IN (SELECT `id` FROM `guild_ranks` WHERE `guild_id` = '.$guild_id.') ')->fetch(); 
		$allM3 = $SQL->query ('SELECT `name` FROM `players` WHERE `rank_id` IN (SELECT `id` FROM `guild_ranks` WHERE `guild_id` = '.$guild_id.') ORDER BY `level` ASC LIMIT 1')->fetch(); 
		$allM4 = $SQL->query ('SELECT `name` FROM `players` WHERE `rank_id` IN (SELECT `id` FROM `guild_ranks` WHERE `guild_id` = '.$guild_id.') ORDER BY `level` DESC LIMIT 1')->fetch(); 
		$invite = $SQL->query( 'SELECT COUNT(*) FROM `guild_invites` WHERE `guild_id` = '.$guild_id.'')->fetch( );     
		$s = $SQL->query('SELECT COUNT(`vocation`) FROM `players` WHERE `rank_id` IN (SELECT `id` FROM `guild_ranks` WHERE `guild_id` = '.$guild_id.') AND vocation = 1')->fetch();       
		$d = $SQL->query('SELECT COUNT(`vocation`) FROM `players` WHERE `rank_id` IN (SELECT `id` FROM `guild_ranks` WHERE `guild_id` = '.$guild_id.') AND vocation = 2')->fetch();
		$p = $SQL->query('SELECT COUNT(`vocation`) FROM `players` WHERE `rank_id` IN (SELECT `id` FROM `guild_ranks` WHERE `guild_id` = '.$guild_id.') AND vocation = 3')->fetch();    
		$k = $SQL->query('SELECT COUNT(`vocation`) FROM `players` WHERE `rank_id` IN (SELECT `id` FROM `guild_ranks` WHERE `guild_id` = '.$guild_id.') AND vocation = 4')->fetch();

Regards.

 

[Gesior.pl]

One query for sum and avg:

SELECT SUM(`level`) as `level_sum`,AVG(`level`) as `level_avg` FROM `players` WHERE `rank_id` IN (SELECT `id` FROM `guild_ranks` WHERE `guild_id` = 1)

EDIT:
Not 4 queries:

SELECT COUNT(`vocation`) FROM `players` WHERE `rank_id` IN (SELECT `id` FROM `guild_ranks` WHERE `guild_id` = 1) AND vocation = 1
...
SELECT COUNT(`vocation`) FROM `players` WHERE `rank_id` IN (SELECT `id` FROM `guild_ranks` WHERE `guild_id` = 1) AND vocation = 4

Use 1 query:

SELECT `vocation`, COUNT(`vocation`) FROM `players` WHERE `rank_id` IN (SELECT `id` FROM `guild_ranks` WHERE `guild_id` = 1) GROUP BY `vocation`

Not:

SELECT COUNT(*) FROM `guild_invites` WHERE `guild_id` = 1

Use (it will be faster, idk why, but I did tests on big database and it was faster when query load only one column [select 1 column and in 'where' same column]):

SELECT COUNT(`guild_id`) FROM `guild_invites` WHERE `guild_id` = 1

(in my queries I use 1 in place of guild_id variable)