rob@XE:11g2> SELECT team, MAX(div) AS div,
2 SUM(DECODE(wlt, 'WIN',1.0, 'LOSS',0, 'TIE',0.5, 0)) AS wins,
3 SUM(ptsf) AS pointsf, SUM(ptsa) AS pointsa, SUM(ptsf)-SUM(ptsa) AS pt_diff
4 FROM nfl2012_scores
5 GROUP BY team
6 ORDER BY 2, 3 DESC, 4 DESC;
TEAM DIV WINS POINTSF POINTSA PT_DIFF
~~~~ ~~~~~~~~~~~~~~~~ ~~~~~~~~~~ ~~~~~~~~~~ ~~~~~~~~~~ ~~~~~~~~~~
NE AFCE 12 557 321 236
MIA AFCE 7 288 317 -29
BUF AFCE 6 344 435 -91
NYJ AFCE 6 271 375 -104
BAL AFCN 10 398 344 54
CIN AFCN 10 391 320 71
PIT AFCN 8 336 314 22
CLE AFCN 5 302 368 -66
HOU AFCS 12 416 331 85
IND AFCS 10 354 390 -36
TEN AFCS 6 330 471 -141
JAC AFCS 2 255 444 -189
DEN AFCW 13 481 289 192
SD AFCW 7 350 350 0
OAK AFCW 4 290 443 -153
KC AFCW 2 214 418 -204
WAS NFCE 10 433 391 42
NYG NFCE 9 429 344 85
DAL NFCE 8 376 400 -24
PHI NFCE 4 280 444 -164
GB NFCN 11 433 336 97
MIN NFCN 10 379 348 31
CHI NFCN 10 375 277 98
DET NFCN 4 372 433 -61
ATL NFCS 13 419 299 120
NO NFCS 7 461 454 7
TB NFCS 7 362 389 -27
CAR NFCS 7 357 363 -6
SF NFCW 11.5 397 273 124
SEA NFCW 11 401 241 160
STL NFCW 7.5 299 348 -49
AZ NFCW 5 250 357 -107
32 rows selected.
rob@XE:11g2> SELECT ptsf AS points_per_game, COUNT(*) AS frequency
2 FROM nfl2012_scores
3 GROUP BY ptsf HAVING COUNT(*) > 1
4 ORDER BY 2 DESC;
POINTS_PER_GAME FREQUENCY
~~~~~~~~~~~~~~~ ~~~~~~~~~~
13 39
24 36
23 34
27 31
17 29
20 27
31 25
34 21
28 19
10 19
14 18
21 16
16 16
7 15
38 15
30 13
6 12
19 12
26 10
3 9
22 8
35 8
9 7
41 7
0 6
37 6
18 5
42 5
12 5
36 4
29 4
15 4
33 3
44 3
32 3
40 2
45 2
52 2
55 2
43 2
25 2
41 rows selected.
rob@XE:11g2> SELECT ABS(ptsf-ptsa) AS victory_margin, COUNT(*) AS frequency
2 FROM nfl2012_scores
3 WHERE venue = 'HOME'
4 GROUP BY ABS(ptsf-ptsa) HAVING COUNT(*) > 1
5 ORDER BY 2 DESC;
VICTORY_MARGIN FREQUENCY
============== =========
3 34
7 25
6 17
10 15
4 13
1 12
8 12
14 11
2 10
17 9
21 8
5 7
18 7
15 6
20 6
28 6
24 5
12 5
35 4
22 4
13 4
25 3
16 3
34 3
23 3
31 3
11 3
19 3
38 2
29 2
9 2
31 rows selected.
rob@XE:11g2> WITH
2 blowouts AS
3 (
4 SELECT team,
5 SUM(DECODE(wlt, 'WIN', 1, 0)) AS Blowout_W,
6 SUM(DECODE(wlt, 'LOSS', 1, 0)) AS Blowout_L
7 FROM nfl2012_scores
8 WHERE ABS(ptsf-ptsa) >= 20
9 GROUP BY team
10 ORDER BY 2 desc
11 ),
12 close_games AS
13 (
14 SELECT team,
15 SUM(DECODE(wlt, 'WIN', 1, 'TIE', .5, 0)) AS Close_W,
16 SUM(DECODE(wlt, 'LOSS', 1, 'TIE', .5, 0)) AS Close_L
17 FROM nfl2012_scores
18 WHERE ABS(ptsf-ptsa) <= 7
19 GROUP BY team
20 ORDER BY 2 desc
21 )
22 SELECT
23 C.team, Blowout_W, Blowout_L,
24 Blowout_W / (Blowout_w + Blowout_L) AS "Blowout_Pct",
25 Close_W, Close_L,
26 Close_W / (Close_W + Close_L) AS "Close_Pct"
27 FROM
28 blowouts B, close_games C
29 WHERE B.team(+) = C.team
30 ORDER BY 2 DESC, 3 ASC, 7 DESC;
TEAM BLOWOUT_W BLOWOUT_L Blowout_Pct CLOSE_W CLOSE_L Close_Pct
~~~~ ~~~~~~~~~~ ~~~~~~~~~~ ~~~~~~~~~~~ ~~~~~~~~~~ ~~~~~~~~~~ ~~~~~~~~~
PIT 5 6 .455
NE 7 0 1.000 4 4 .500
SEA 5 0 1.000 5 4 .556
DEN 5 0 1.000 1 2 .333
NYG 5 1 .833 3 4 .429
HOU 4 1 .800 5 0 1.000
SF 4 2 .667 2.5 1.5 .625
CIN 3 1 .750 5 3 .625
CHI 3 1 .750 3 3 .500
MIA 3 3 .500 3 5 .375
ATL 2 0 1.000 7 2 .778
BAL 2 1 .667 6 4 .600
NO 2 2 .500 4 3 .571
AZ 2 2 .500 3 4 .429
NYJ 2 4 .333 3 3 .500
MIN 1 0 1.000 5 1 .833
WAS 1 0 1.000 5 4 .556
GB 1 1 .500 3 3 .500
CLE 1 1 .500 3 5 .375
TB 1 2 .333 3 5 .375
SD 1 2 .333 1 5 .167
CAR 1 2 .333 1 7 .125
TEN 1 6 .143 4 3 .571
DAL 0 1 .000 7 5 .583
STL 0 1 .000 4.5 3.5 .563
DET 0 1 .000 3 9 .250
IND 0 3 .000 8 2 .800
KC 0 3 .000 2 3 .400
JAC 0 3 .000 2 5 .286
PHI 0 4 .000 4 4 .500
BUF 0 4 .000 2 4 .333
OAK 0 5 .000 2 3 .400
32 rows selected.