Penalty Records - GROUP BY; HAVING; UNION;

Query for yellow card:

with a given Group, so its id is given.

with a given Event name, so its e_id is given

with the current season for each tournament, so the 5 seasons' ids are given, then the matchDays' ids can be inferred.

 

SELECT me.*, t.t_name, pl.first_name, pl.last_name FROM jos_bl_match_events AS me, jos_bl_teams AS t, jos_bl_players AS pl,jos_bl_grteams AS gt,jos_bl_match AS mWHERE me.e_id = 1 AND me.t_id = t.id AND me.player_id = pl.id AND gt.t_id = me.t_id AND gt.g_id = 4 AND m.id = me.match_id AND m.m_id in (2,3)

SUM

SELECT SUM(me.ecount) AS yellowc, me.*, t.t_name, pl.first_name, pl.last_name FROM jos_bl_match_events AS me, jos_bl_teams AS t, jos_bl_players AS pl,jos_bl_grteams AS gt,jos_bl_match AS mWHERE me.e_id = 1 AND me.t_id = t.id AND me.player_id = pl.id AND gt.t_id = me.t_id AND gt.g_id = 4 AND m.id = me.match_id AND m.m_id in (2,3) GROUP BY me.player_id

Refs:

http://www.java2s.com/Code/SQL/CatalogSQL.htm(important)

http://www.tizag.com/sqlTutorial/sqlgroupby.php

http://webdesign.kerthis.com/sql/sql_left_join(tw)

http://xianglp.iteye.com/blog/868957

Oracle

http://www.java2s.com/Tutorial/Oracle/0040__Query-Select/UsingMultipleColumnsinaGroup.htm

http://www.java2s.com/Tutorial/Oracle/0040__Query-Select/GroupingatMultipleLevels.htm

http://www.java2s.com/Tutorial/Oracle/0040__Query-Select/Bornafter19600101groupbydepartmentnumberwithcount4.htm

http://www.java2s.com/Tutorial/Oracle/0040__Query-Select/GettingValuesandSubtotalsinOneGowithUNION.htm