Product Documentation

Appearance

Subquery Examples ​

Yellowbrick supports subqueries in the select list, FROM clause, WHERE clause, and WITH clause. See also SQL Conditions and Correlated Subqueries.

The following FROM clause subquery finds results from the match table where the half-time and full-time scores were the same for a given season and home team.

premdb=# select * from 
(select seasonid, htid, atid, concat_ws(' : ',htscore,ftscore) score
   from match where htid=45 and seasonid=10) s 
where substr(score,1,3)=substr(score,7,9);

 seasonid | htid | atid |   score   
----------+------+------+-----------
      10 |   45 |   51 | 1-1 : 1-1
      10 |   45 |   62 | 2-0 : 2-0
      10 |   45 |   63 | 2-1 : 2-1
      10 |   45 |   71 | 0-0 : 0-0
      10 |   45 |   72 | 1-0 : 1-0
(5 rows)

The following query uses two subqueries in the FROM clause to compares goals scored at home and away for each team during a specific season:

premdb=# select a.name, homegoals, awaygoals, 
homegoals+awaygoals as goals_for
from 
(select t1.name, sum(substr(ftscore,1,1)::int) 
from match m1, team t1, season s1
where (m1.htid=t1.htid and m1.seasonid=s1.seasonid)
and season_name='2003-2004'
group by t1.name order by 2 desc) as a(name,homegoals),
(select t2.name, sum(substr(ftscore,3,1)::int) 
from match m2, team t2, season s2
where (m2.atid=t2.atid and m2.seasonid=s2.seasonid)
and season_name='2003-2004'
group by t2.name) as b(name,awaygoals)
where a.name=b.name
order by 4 desc;
         name           | homegoals | awaygoals | goals_for 
-------------------------+-----------+-----------+-----------
 Arsenal                 |        40 |        33 |        73
 Chelsea                 |        34 |        33 |        67
 Manchester United       |        37 |        27 |        64
 Manchester City         |        31 |        24 |        55
 Liverpool               |        29 |        26 |        55
 Newcastle United        |        33 |        19 |        52
 Fulham                  |        29 |        23 |        52
 Blackburn Rovers        |        25 |        26 |        51
 Charlton Athletic       |        29 |        22 |        51
 Bolton Wanderers        |        24 |        24 |        48
 Leicester City          |        19 |        29 |        48
 Aston Villa             |        24 |        24 |        48
 Tottenham Hotspur       |        33 |        14 |        47
 Portsmouth              |        35 |        12 |        47
 Everton                 |        27 |        18 |        45
 Middlesbrough           |        25 |        19 |        44
 Southampton             |        24 |        20 |        44
 Birmingham City         |        26 |        17 |        43
 Leeds United            |        25 |        15 |        40
 Wolverhampton Wanderers |        23 |        15 |        38
(20 rows)

Parent topic:Subqueries