I need to select team that is the top scorer in the league. this is the match column, it does depends on m_id if you are team1_id or team2_id same with scores as there are rematches too in the league.
CREATE TABLE IF NOT EXISTS `xeg8u_bl_match` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`m_id` int(11) NOT NULL DEFAULT '0',
`team1_id` int(11) NOT NULL DEFAULT '0',
`team2_id` int(11) NOT NULL DEFAULT '0',
`score1` int(11) NOT NULL DEFAULT '0',
`score2` int(11) NOT NULL DEFAULT '0',
`match_descr` text NOT NULL,
`published` char(1) NOT NULL DEFAULT '0',
`is_extra` char(1) NOT NULL DEFAULT '0',
`m_played` char(1) NOT NULL DEFAULT '0',
`m_date` date NOT NULL DEFAULT '0000-00-00',
`m_time` varchar(10) NOT NULL DEFAULT '',
`m_location` varchar(255) NOT NULL,
`bonus1` decimal(10,2) NOT NULL DEFAULT '0.00',
`bonus2` decimal(10,2) NOT NULL DEFAULT '0.00',
`team1_strzaly` varchar(255) NOT NULL,
`team1_strzalyc` varchar(255) NOT NULL,
`team1_posiadanie` varchar(255) NOT NULL,
`team1_przejecia` varchar(255) NOT NULL,
`team1_faule` varchar(255) NOT NULL,
`team1_zkartki` varchar(255) NOT NULL,
`team1_ckartki` varchar(255) NOT NULL,
`team1_kontuzje` varchar(255) NOT NULL,
`team1_spalone` varchar(255) NOT NULL,
`team1_rrozne` varchar(255) NOT NULL,
`team1_celnosc` varchar(255) NOT NULL,
`team1_dpodan` varchar(255) NOT NULL,
`team2_strzaly` varchar(255) NOT NULL,
`team2_strzalyc` varchar(255) NOT NULL,
`team2_posiadanie` varchar(255) NOT NULL,
`team2_przejecia` varchar(255) NOT NULL,
`team2_faule` varchar(255) NOT NULL,
`team2_zkartki` varchar(255) NOT NULL,
`team2_ckartki` varchar(255) NOT NULL,
`team2_kontuzje` varchar(255) NOT NULL,
`team2_spalone` varchar(255) NOT NULL,
`team2_rrozne` varchar(255) NOT NULL,
`team2_celnosc` varchar(255) NOT NULL,
`team2_dpodan` varchar(255) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 AUTO_INCREMENT=399 ;
Now I was thinking about using two function in SELECT, MAX and SUM, I was trying to get this but I have no idea how I can get this to work, I know how to display goals scored home and away for specified team but I dont know how can I check which team is a top scorrer. Any ideas?
So I work it out an it does work however my question is if there is any way to do it easier and if it is needed to do it different way?
//season_id
$link = ConnectToMySql();
$wynik = mysqli_query($link, "SELECT season_id FROM xeg8u_bl_season_teams WHERE team_id = '$teamekid' LIMIT 1");
$m_id = mysqli_fetch_array($wynik);
$r4 = $m_id[0];
//sprawdzam jakie id dni meczowych znajdują się w sezonie o id x
$link = ConnectToMySql();
$wynik = mysqli_query($link, "SELECT MIN(id) FROM xeg8u_bl_matchday WHERE s_id = '$r4'");
$m_id = mysqli_fetch_array($wynik);
$min_r1 = $m_id[0];
$link = ConnectToMySql();
$wynik = mysqli_query($link, "SELECT MAX(id) FROM xeg8u_bl_matchday WHERE s_id = '$r4'");
$m_id = mysqli_fetch_array($wynik);
$max_r1 = $m_id[0];
$link = ConnectToMySql();
$wynik = mysqli_query($link, "SELECT m_id FROM xeg8u_bl_match WHERE m_id LIKE '$r1'");
$id = mysqli_fetch_array($wynik);
$r2 = $id[0];
$link = ConnectToMySql();
$wynik = mysqli_query($link, "Select team, sum(score) as totalscore from (select team1_id as team, sum(score1) as score from xeg8u_bl_match WHERE m_id BETWEEN '$min_r1' AND '$max_r1' group by team1_id union all select team2_id as team, sum(score2) as score from xeg8u_bl_match WHERE m_id BETWEEN '$min_r1' AND '$max_r1' group by team2_id) as scores group by team order by totalscore asc limit 1");
$krol = mysqli_fetch_array($wynik);
$r3 = $krol[0];
Is that fine to leave it like this or there is a simpler, better looking way to do it?
I suppose you need this:
select team, sum(score) as totalscore
from
(
select team1_id as team, sum(score1) as score
from xeg8u_bl_match
group by team1_id
union all
select team2_id as team, sum(score2) as score
from xeg8u_bl_match
group by team2_id
) as scores
group by team
order by 2 desc
limit 1