ReSQL.ru
     
powered by simpleCommunicator - 2.0.61     © 2026 Programmizd 02
Мобильная версия    Контакт    Правила    FAQ    Помощь
Целевая тема:
Создать новую тему:
Автор:
Закрыть
Цитировать
Доб. в избранное | Игнор. тему | Прикреп. тему | Пометить прочит. / непрочит. | Фильтр
Форумы / MySQL [игнор отключен] [закрыт для гостей] / How to find overall point of each team from result
22 сообщений из 22, страница 1 из 1
How to find overall point of each team from result
    #38397645
Shahriyar.R
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Shahriyar.R
Гость
Dear experts at this moment i have an issue with finding overall of each team from futball related database:

Here is my tables wd_timeline it is for track what happened during match:

Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
CREATE TABLE `wd_timeline` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `match` int(11) NOT NULL,
  `type` tinyint(3) unsigned NOT NULL,
  `team` int(11) NOT NULL,
  `player1` int(11) unsigned NOT NULL,
  `player2` int(10) unsigned NOT NULL,
  `goal` tinyint(1) DEFAULT NULL,
  `time` tinyint(1) DEFAULT NULL,
  `minute` tinyint(4) NOT NULL,
  `plus` tinyint(4) NOT NULL,
  `about` varchar(250) NOT NULL,
  `ext` char(5) NOT NULL,
  `status` tinyint(1) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=140 DEFAULT CHARSET=utf8




Sample output from wd_timeline:




And table wd_matches:
Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
CREATE TABLE `wd_matches` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `home` int(11) NOT NULL,
  `away` int(11) NOT NULL,
  `type` tinyint(1) NOT NULL,
  `stadium` smallint(5) unsigned NOT NULL,
  `referee` mediumint(8) unsigned NOT NULL,
  `match_date` datetime NOT NULL,
  `about` text NOT NULL,
  `ext` char(5) NOT NULL,
  `status` tinyint(1) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=31 DEFAULT CHARSET=utf8



Sample Output from wd_matches:



Table wd_teams:
Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
 CREATE TABLE `wd_teams` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `slug` varchar(25) NOT NULL,
  `name` varchar(250) NOT NULL,
  `about` text NOT NULL,
  `site` varchar(250) NOT NULL,
  `founded` date NOT NULL,
  `country` smallint(5) unsigned NOT NULL,
  `city` mediumint(8) unsigned NOT NULL,
  `stadium` int(11) NOT NULL,
  `ext` varchar(5) NOT NULL,
  `status` tinyint(1) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=11 DEFAULT CHARSET=utf8



From wd_teams:



And finally Here is my query for finding out which team wins or stay draw(ничья):

Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
28.
29.
30.
31.
32.
33.
34.
35.
36.
37.
38.
39.
SELECT 
    (SELECT name FROM wd_teams WHERE id = t2.home) home,
    (SELECT name FROM wd_teams WHERE id = t2.away) away,
    COALESCE(t2.home_goals, 0) home_goals,
    COALESCE(t2.away_goals, 0) away_goals,
    IF(t2.home_goals > coalesce(t2.away_goals, 0),1,0) is_home_qalib,
    IF(t2.away_goals > coalesce(t2.home_goals, 0),1,0) is_away_qalib
	
FROM
    (SELECT 
        t1.id, t1.home, t1.away, t1.home_goals, t1.away_goals
    FROM
        (SELECT 
			m.id,
            m.home,
            m.away,
			
            (SELECT COUNT(goal)
                FROM
                    wd_timeline
                WHERE
                    wd_timeline.match = m.id
                        AND team = m.home
                        AND TYPE = 0
                GROUP BY team) home_goals,
            (SELECT 
                    COUNT(goal)
                FROM
                    wd_timeline
                WHERE
                    wd_timeline.match = m.id
                        AND team = m.away
                        AND TYPE = 0
                GROUP BY team) away_goals
    FROM
        `wd_matches` m) t1
    WHERE
        t1.home_goals IS NOT NULL
            OR t1.away_goals IS NOT NULL) as t2;





Output from final query:




So now i want to count a final point for each team.
For eg: team "Qebele" wins 1 time for "away" and 1 time for "home" it will mean 6 points for final result. How i can accomplish this?
Or maybe you suggest different query for this purpose. Any help appreciated. Thanks.
...
Рейтинг: 0 / 0
16.09.2013, 13:28:26
| Ответить | Цитировать | Написать  
How to find overall point of each team from result
    #38397782
Cygapb-007
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Cygapb-007
Участник
Shahriyar.R,
Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
select 
  h.name home, a.name away, g.home_goal, g.away_goal, 
  if(g.home_goal>away_goal,1,0) is_home_wins,
  if(g.home_goal<away_goal,1,0) is_away_wins
from (
  select 
    m.id, m.home, m.away,
    sum(if(l.team=m.home,l.goal,0))home_goal, 
    sum(if(l.team=m.away,l.goal,0))away_goal
  from wd_matches m
  join wd_timeline l on l.match=m.id
  group by m.id, m.home, m.away
  )g
join wd_teams h on h.id=g.home
join wd_teams a on a.id=g.away


Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
select 
  g.name,
  sum(if(g.is_home=1 and g.wins>0,1,0)) home_win,
  sum(if(g.is_home=0 and g.wins>0,1,0)) away_win,
  sum(if(g.is_home=1 and g.wins=0,1,0)) home_draw,
  sum(if(g.is_home=0 and g.wins=0,1,0)) away_draw
from (
  select 
    t.name, l.match, if(m.home=t.id,1,0) is_home, 
    sum(if(t.id=l.team,goal,-goal))wins
  from wd_teams t
  join wd_matches m on t.id in (m.home, m.away)
  join wd_timeline l on l.match=m.id
  group by t.id, l.match, if(m.home=t.id,1,0)
  )g
group by g.id
...
Рейтинг: 0 / 0
16.09.2013, 15:10:00
| Ответить | Цитировать | Написать  
How to find overall point of each team from result
    #38397800
Cygapb-007
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Cygapb-007
Участник
Cygapb-007
Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
select 
  g.name,
  sum(if(g.is_home=1 and g.wins>0,1,0)) home_win,
  sum(if(g.is_home=0 and g.wins>0,1,0)) away_win,
  sum(if(g.is_home=1 and g.wins=0,1,0)) home_draw,
  sum(if(g.is_home=0 and g.wins=0,1,0)) away_draw
from (
  select 
    t.id, t.name, l.match, if(m.home=t.id,1,0) is_home, 
    sum(if(t.id=l.team,goal,-goal))wins
  from wd_teams t
  join wd_matches m on t.id in (m.home, m.away)
  join wd_timeline l on l.match=m.id
  group by t.id, l.match, if(m.home=t.id,1,0)
  )g
group by g.id
...
Рейтинг: 0 / 0
16.09.2013, 15:16:27
| Ответить | Цитировать | Написать  
How to find overall point of each team from result
    #38397805
Shahriyar.R
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Shahriyar.R
Гость
Cygapb-007,

Please explain your queries. i cant figure out what happened here :)

For my question, i solve this with this query:

Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
28.
29.
30.
31.
32.
33.
34.
35.
36.
37.
38.
39.
40.
41.
42.
43.
44.
45.
46.
47.
48.
49.
50.
51.
52.
53.
54.
55.
56.
57.
58.
59.
60.
61.
62.
63.
64.
65.
66.
67.
68.
69.
70.
71.
72.
73.
74.
75.
76.
77.
78.
79.
80.
81.
82.
83.
84.
85.
86.
87.
88.
89.
90.
91.
92.
93.
94.
95.
96.
97.
select 
    total_score.team,
	sum(total_score.game_score) team_score
from
    (select 
        a.home team,
            case
                when a.is_home_qalib = 1 then 3
                when a.is_home_qalib = a.is_away_qalib then 1
                else 0
            end game_score
    from (SELECT 
    (SELECT name FROM wd_teams WHERE id = t2.home) home,
    (SELECT name FROM wd_teams WHERE id = t2.away) away,
    COALESCE(t2.home_goals, 0) home_goals,
    COALESCE(t2.away_goals, 0) away_goals,
    IF(t2.home_goals > coalesce(t2.away_goals, 0),1,0) is_home_qalib,
    IF(t2.away_goals > coalesce(t2.home_goals, 0),1,0) is_away_qalib
FROM
    (SELECT 
        t1.id, t1.home, t1.away, t1.home_goals, t1.away_goals
    FROM
        (SELECT 
        m.id,
            m.home,
            m.away,
            (SELECT COUNT(goal)
                FROM
                    wd_timeline
                WHERE
                    wd_timeline.match = m.id
                        AND team = m.home
                        AND TYPE = 0
                GROUP BY team) home_goals,
            (SELECT 
                    COUNT(goal)
                FROM
                    wd_timeline
                WHERE
                    wd_timeline.match = m.id
                        AND team = m.away
                        AND TYPE = 0
                GROUP BY team) away_goals
    FROM
        `wd_matches` m) t1
    WHERE
        t1.home_goals IS NOT NULL
            OR t1.away_goals IS NOT NULL) t2) as a

	union all
	select 
        b.away team,
            case
                when b.is_away_qalib = 1 then 3
                when b.is_home_qalib = b.is_away_qalib then 1
                else 0
            end game_score
    from
        (SELECT 
    (SELECT name FROM wd_teams WHERE id = t2.home) home,
    (SELECT name FROM wd_teams WHERE id = t2.away) away,
    COALESCE(t2.home_goals, 0) home_goals,
    COALESCE(t2.away_goals, 0) away_goals,
    IF(t2.home_goals > coalesce(t2.away_goals, 0),1,0) is_home_qalib,
    IF(t2.away_goals > coalesce(t2.home_goals, 0),1,0) is_away_qalib
FROM
    (SELECT 
        t1.id, t1.home, t1.away, t1.home_goals, t1.away_goals
    FROM
        (SELECT 
        m.id,
            m.home,
            m.away,
            (SELECT COUNT(goal)
                FROM
                    wd_timeline
                WHERE
                    wd_timeline.match = m.id
                        AND team = m.home
                        AND TYPE = 0
                GROUP BY team) home_goals,
            (SELECT 
                    COUNT(goal)
                FROM
                    wd_timeline
                WHERE
                    wd_timeline.match = m.id
                        AND team = m.away
                        AND TYPE = 0
                GROUP BY team) away_goals
    FROM
        `wd_matches` m) t1
    WHERE
        t1.home_goals IS NOT NULL
            OR t1.away_goals IS NOT NULL) t2) as b) as total_score
group by total_score.team
order by team_score desc;




Output:

Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
+--------------------+------------+
| team               | team_score |
+--------------------+------------+
| Neft&#231;i             |          9 |
| Qaraba&#287;            |          6 |
| Q&#601;b&#601;l&#601;             |          6 |
| Simurq             |          3 |
| AZAL               |          3 |
| X&#601;z&#601;r L&#601;nk&#601;ran     |          1 |
| Sumqay&#305;t           |          1 |
| R&#601;van              |          0 |
| &#304;nter              |          0 |
| Bak&#305;               |          0 |
+--------------------+------------+
10 rows in set (0.02 sec)
...
Рейтинг: 0 / 0
16.09.2013, 15:19:47
| Ответить | Цитировать | Написать  
How to find overall point of each team from result
    #38397822
Shahriyar.R
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Shahriyar.R
Гость
Cygapb-007,

I edited slightly your last query and result is EXCELLENT!

Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
select 
  g.name,
  (sum(if(g.is_home=1 and g.wins>0,1,0))*3 +
  sum(if(g.is_home=0 and g.wins>0,1,0))*3 +
  sum(if(g.is_home=1 and g.wins=0,1,0))*1 +
  sum(if(g.is_home=0 and g.wins=0,1,0))*1) as final_score
from (
  select 
    t.id, 
	t.name,
	l.match,
	if(m.home=t.id,1,0) is_home, 
    sum(if(t.id=l.team,goal,-goal))wins
  from wd_teams t
  join wd_matches m on t.id in (m.home, m.away)
  join wd_timeline l on l.match=m.id
  group by t.id, l.match, if(m.home=t.id,1,0)
  )g
group by g.id
order by final_score desc;
...
Рейтинг: 0 / 0
16.09.2013, 15:32:36
| Ответить | Цитировать | Написать  
How to find overall point of each team from result
    #38397853
hallabud
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
hallabud
Участник
Shahriyar.R,

Add scripts for insert the sample data please.
...
Рейтинг: 0 / 0
16.09.2013, 16:06:19
| Ответить | Цитировать | Написать  
How to find overall point of each team from result
    #38397854
Shahriyar.R
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Shahriyar.R
Гость
Cygapb-007,

Also added total match_count:

Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
select 
  g.name,
  count(`match`) as match_count,
  (sum(if(g.is_home=1 and g.wins>0,1,0))*3 +
  sum(if(g.is_home=0 and g.wins>0,1,0))*3 +
  sum(if(g.is_home=1 and g.wins=0,1,0))*1 +
  sum(if(g.is_home=0 and g.wins=0,1,0))*1) as final_score
from (
  select 
    t.id, 
	t.name,
	l.match,
	if(m.home=t.id,1,0) is_home, 
    sum(if(t.id=l.team,goal,-goal))wins
  from wd_teams t
  join wd_matches m on t.id in (m.home, m.away)
  join wd_timeline l on l.match=m.id
  group by t.id, l.match, if(m.home=t.id,1,0)
  )g
group by g.id
order by final_score desc;




Output:

...
Рейтинг: 0 / 0
16.09.2013, 16:07:07
| Ответить | Цитировать | Написать  
How to find overall point of each team from result
    #38397864
Cygapb-007
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Cygapb-007
Участник
Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
select 
  g.name, sum(g.score)final_score
from(
  select 
    t.id, t.name,
    sign(sum(if(t.id=l.team,goal,-goal)))*2+1 score
  from wd_teams t
  join wd_matches m on t.id in (m.home, m.away)
  join wd_timeline l on l.match=m.id
  group by t.id, l.match
  having sum(if(t.id=l.team,goal,-goal))>=0
  )g
group by g.id
order by final_score desc;
...
Рейтинг: 0 / 0
16.09.2013, 16:12:35
| Ответить | Цитировать | Написать  
How to find overall point of each team from result
    #38397877
Shahriyar.R
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Shahriyar.R
Гость
Cygapb-007
Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
select 
  g.name, sum(g.score)final_score
from(
  select 
    t.id, t.name,
    sign(sum(if(t.id=l.team,goal,-goal)))*2+1 score
  from wd_teams t
  join wd_matches m on t.id in (m.home, m.away)
  join wd_timeline l on l.match=m.id
  group by t.id, l.match
  having sum(if(t.id=l.team,goal,-goal))>=0
  )g
group by g.id
order by final_score desc;



Output truncates 0 point commands:




But previous one works great. Thanks
...
Рейтинг: 0 / 0
16.09.2013, 16:20:49
| Ответить | Цитировать | Написать  
How to find overall point of each team from result
    #38397885
Shahriyar.R
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Shahriyar.R
Гость
hallabud,

sure:

https://www.dropbox.com/s/p728ju4uyhv8d4e/Dump20130916.zip
...
Рейтинг: 0 / 0
16.09.2013, 16:25:12
| Ответить | Цитировать | Написать  
How to find overall point of each team from result
    #38397929
Shahriyar.R
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Shahriyar.R
Гость
Dear Cygapb-007 thanks for great assist i have nearly finished this task:


Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
select 
  g.name,
  count(`match`) as total_matchs,
  (sum(if(g.is_home=1 and g.wins>0,1,0))+
  sum(if(g.is_home=0 and g.wins>0,1,0))) as wins,
  (sum(if(g.is_home=1 and g.wins=0,1,0))+
  sum(if(g.is_home=0 and g.wins=0,1,0))) as draw,
(count(`match`)-((sum(if(g.is_home=1 and g.wins>0,1,0))+
  sum(if(g.is_home=0 and g.wins>0,1,0)))+(sum(if(g.is_home=1 and g.wins=0,1,0))+
  sum(if(g.is_home=0 and g.wins=0,1,0))))) as lost,
((sum(if(g.is_home=1 and g.wins>0,1,0))*3 +
  sum(if(g.is_home=0 and g.wins>0,1,0))*3 +
  sum(if(g.is_home=1 and g.wins=0,1,0))*1 +
  sum(if(g.is_home=0 and g.wins=0,1,0))*1)) as total_point
from (
  select 
    t.id, t.name, l.match, if(m.home=t.id,1,0) is_home, 
    sum(if(t.id=l.team,goal,-goal))wins
  from wd_teams t
  join wd_matches m on t.id in (m.home, m.away)
  join wd_timeline l on l.match=m.id
  group by t.id, l.match, if(m.home=t.id,1,0)
  )g
group by g.id
order by total_point desc;








For completion of task i need the last thing the Goals, which each command get: goal forward(GF) and goal against(GA). (забил) (пропустил)
...
Рейтинг: 0 / 0
16.09.2013, 17:01:46
| Ответить | Цитировать | Написать  
How to find overall point of each team from result
    #38397987
Cygapb-007
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Cygapb-007
Участник
Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
from (
  select 
    t.id, t.name, l.match, if(m.home=t.id,1,0) is_home, 
    sum(if(t.id=l.team,1,-1))wins,
    sum(t.id=l.team)GF,
    sum(t.id<>l.team))GA
  from wd_teams t
  join wd_matches m on t.id in (m.home, m.away)
  join wd_timeline l on l.match=m.id and l.goal=1
  group by t.id, l.match, if(m.home=t.id,1,0)
  )g
...
Рейтинг: 0 / 0
16.09.2013, 18:04:45
| Ответить | Цитировать | Написать  
How to find overall point of each team from result
    #38398186
Shahriyar.R
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Shahriyar.R
Гость
Dear Cygapb-007,

As you stated last, i change "from" clause:

Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
28.
29.
select 
  g.name,
  count(`match`) as total_matchs,
  (sum(if(g.is_home=1 and g.wins>0,1,0))+
  sum(if(g.is_home=0 and g.wins>0,1,0))) as wins,
  (sum(if(g.is_home=1 and g.wins=0,1,0))+
  sum(if(g.is_home=0 and g.wins=0,1,0))) as draw,
(count(`match`)-((sum(if(g.is_home=1 and g.wins>0,1,0))+
  sum(if(g.is_home=0 and g.wins>0,1,0)))+(sum(if(g.is_home=1 and g.wins=0,1,0))+
  sum(if(g.is_home=0 and g.wins=0,1,0))))) as lost,
((sum(if(g.is_home=1 and g.wins>0,1,0))*3 +
  sum(if(g.is_home=0 and g.wins>0,1,0))*3 +
  sum(if(g.is_home=1 and g.wins=0,1,0))*1 +
  sum(if(g.is_home=0 and g.wins=0,1,0))*1)) as total_point,
g.GF,
g.GA
from (
  select 
    t.id, t.name, l.match, if(m.home=t.id,1,0) as is_home, 
    sum(if(t.id=l.team,1,-1)) as wins,
    sum(t.id=l.team) as GF,
    sum(t.id<>l.team) as GA
  from wd_teams t
  join wd_matches m on t.id in (m.home, m.away)
  join wd_timeline l on l.match=m.id and l.goal=1
  group by t.id, l.match, if(m.home=t.id,1,0)
  )g
group by g.id
order by total_point desc;




Output from query:





But actually "Qebele" has 6 goals:



How we can fix it?
...
Рейтинг: 0 / 0
16.09.2013, 22:49:12
| Ответить | Цитировать | Написать  
How to find overall point of each team from result
    #38398196
Cygapb-007
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Cygapb-007
Участник
Код: sql
1.
2.
3.
4.
...
  sum(g.GF)GF,
  sum(g.GA)GA
from ...
...
Рейтинг: 0 / 0
16.09.2013, 23:02:36
| Ответить | Цитировать | Написать  
How to find overall point of each team from result
    #38398199
Shahriyar.R
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Shahriyar.R
Гость
Cygapb-007,

uupppssss ))))) Great!
...
Рейтинг: 0 / 0
16.09.2013, 23:09:38
| Ответить | Цитировать | Написать  
How to find overall point of each team from result
    #38398200
Shahriyar.R
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Shahriyar.R
Гость
Cygapb-007,

how i can email or PM you?
...
Рейтинг: 0 / 0
16.09.2013, 23:11:07
| Ответить | Цитировать | Написать  
How to find overall point of each team from result
    #38398208
Cygapb-007
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Cygapb-007
Участник
Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
select 
  t.name,
  count(*) as total_matchs,
  sum(g.wins>0) as wins,
  sum(g.wins=0) as draw,
  sum(g.wins<0) as lost,
  sum(case sign(g.wins)
    when 1 then 3
    when 0 then 1
    else 0 end) as total_point,
  sum(g.GF)GF,
  sum(g.GA)GA
from (
  select 
    t.id, l.match, 
    sum(if(t.id=l.team,1,-1)) as wins,
    sum(t.id=l.team) as GF,
    sum(t.id<>l.team) as GA
  from wd_teams t
  join wd_matches m on t.id in (m.home, m.away)
  join wd_timeline l on l.match=m.id and l.goal=1
  group by t.id, l.match
  )g
join wd_teams t on t.id=g.id
group by t.id, t.name
order by total_point desc;
...
Рейтинг: 0 / 0
16.09.2013, 23:24:59
| Ответить | Цитировать | Написать  
How to find overall point of each team from result
    #38406089
Shahriyar.R
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Shahriyar.R
Гость
Cygapb-007
Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
select 
  t.name,
  count(*) as total_matchs,
  sum(g.wins>0) as wins,
  sum(g.wins=0) as draw,
  sum(g.wins<0) as lost,
  sum(case sign(g.wins)
    when 1 then 3
    when 0 then 1
    else 0 end) as total_point,
  sum(g.GF)GF,
  sum(g.GA)GA
from (
  select 
    t.id, l.match, 
    sum(if(t.id=l.team,1,-1)) as wins,
    sum(t.id=l.team) as GF,
    sum(t.id<>l.team) as GA
  from wd_teams t
  join wd_matches m on t.id in (m.home, m.away)
  join wd_timeline l on l.match=m.id and l.goal=1
  group by t.id, l.match
  )g
join wd_teams t on t.id=g.id
group by t.id, t.name
order by total_point desc;



Dear Cygapb-007 everything was ok but occasionally i found out that total_matchs doesnt match actual match count.
From query above:



But actually all commands play 6 matches. Here is eg of "Xezer Lenkeran":

Код: sql
1.
SELECT * FROM wd_matches where home=9 or away=9;





So how i can fix it to count total_matches correctly?
...
Рейтинг: 0 / 0
24.09.2013, 14:21:20
| Ответить | Цитировать | Написать  
How to find overall point of each team from result
    #38406528
Cygapb-007
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Cygapb-007
Участник
SorryCygapb-007
Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
select 
  t.name,
  count(*) as total_matchs,
  sum(g.wins>0) as wins,
  sum(g.wins=0) as draw,
  sum(g.wins<0) as lost,
  sum(case sign(g.wins)
    when 1 then 3
    when 0 then 1
    else 0 end) as total_point,
  sum(g.GF)GF,
  sum(g.GA)GA
from (
  select 
    t.id, l.match, 
    sum(if(t.id=l.team,l.goal,-l.goal)) as wins,
    sum(t.id=l.team and l.goal=1) as GF,
    sum(t.id<>l.team and l.goal=1) as GA
  from wd_teams t
  join wd_matches m on t.id in (m.home, m.away)
  join wd_timeline l on l.match=m.id -- and l.goal=1
  group by t.id, l.match
  )g
join wd_teams t on t.id=g.id
group by t.id, t.name
order by total_point desc;
...
Рейтинг: 0 / 0
24.09.2013, 19:18:47
| Ответить | Цитировать | Написать  
How to find overall point of each team from result
    #38406632
Elshad Agayev
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Elshad Agayev
Гость
Это тебе поможетselect
t . *, (t.wins * 3 + t.draw * 1) score
from
(select
(select
name
from
wd_teams
where
id = t.team) team_name,
(count(t.match)) total_matches,
sum(t.team_goal) gf,
sum(t.ga) ga,
sum(t.wins) wins,
sum(t.lost) lost,
sum(t.draw) draw
from
(select
t . *,
(total_goals - t.team_goal) < team_goal wins,
(total_goals - t.team_goal) > team_goal lost,
(total_goals - t.team_goal) = team_goal draw,
(total_goals - t.team_goal) GA
from
(SELECT
tl.id,
tl.match,
tl.team,
sum(goal) team_goal,
(select
sum(tl2.goal)
from
wd_timeline tl2
where
tl2.match = tl.match) total_goals
FROM
`wd_timeline` tl
group by tl.match , tl.team) t) t
group by t.team) t
...
Рейтинг: 0 / 0
24.09.2013, 22:53:42
| Ответить | Цитировать | Написать  
How to find overall point of each team from result
    #38406636
Elshad Agayev
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Elshad Agayev
Гость
Более аккуратном форме :) я новичок в форуме надеюсь вы поймете меня

Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
28.
29.
30.
31.
32.
33.
34.
35.
36.
37.
38.
39.
select 
    t . *, (t.wins * 3 + t.draw * 1) score
from
    (select 
        (select 
                    name
                from
                    wd_teams
                where
                    id = t.team) team_name,
            (count(t.match)) total_matches,
            sum(t.team_goal) gf,
            sum(t.ga) ga,
            sum(t.wins) wins,
            sum(t.lost) lost,
            sum(t.draw) draw
    from
        (select 
        t . *,
            (total_goals - t.team_goal) < team_goal wins,
            (total_goals - t.team_goal) > team_goal lost,
            (total_goals - t.team_goal) = team_goal draw,
            (total_goals - t.team_goal) GA
    from
        (SELECT 
        tl.id,
            tl.match,
            tl.team,
            sum(goal) team_goal,
            (select 
                    sum(tl2.goal)
                from
                    wd_timeline tl2
                where
                    tl2.match = tl.match) total_goals
    FROM
        `wd_timeline` tl
    group by tl.match , tl.team) t) t
    group by t.team) t
...
Рейтинг: 0 / 0
24.09.2013, 23:01:26
| Ответить | Цитировать | Написать  
How to find overall point of each team from result
    #38406958
Shahriyar.R
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Shahriyar.R
Гость
-- Dear Cygapb-007 thank you for great assist, "профессионализм проявляет себя" :)
-- Elshad Agayev , thanks for a anothter option.
...
Рейтинг: 0 / 0
25.09.2013, 11:17:42
| Ответить | Цитировать | Написать  
22 сообщений из 22, страница 1 из 1
Форумы / MySQL [игнор отключен] [закрыт для гостей] / How to find overall point of each team from result
Найденые пользователи ...
Разблокировать пользователей ...
Читали тему (0):
Читали форум (0):
Пользователи онлайн (0):
Польз. соглашение   Полит. конфиден.  
NoSQL.ru       Кролег: Проекты, Новости, Чат       РЕД ФОРУМ       Фотоальбом: Участники, Встречи
Закрыть
start [/forum/topic.php?fid=47&msg=38406528&tid=1835981]:
 0ms
get settings:
 10ms
get forum list:
 17ms
check forum access:
 3ms
check topic access:
 3ms
track hit:
 43ms
get topic data:
 9ms
get forum data:
 2ms
get page messages:
 54ms
get tp. blocked users:
 1ms
others: 241ms
total:  383ms
созд. / загр.: 383ms
x
x
Закрыть


Просмотр
0 / 0
Close
Debug Console [Select Text]