SQL Exercise: Number of goals scored during a normal play schedule
11. From the following table, write a SQL query to find the number of goal scored in every match within normal play schedule. Sort the result-set on match number. Return match number, number of goal scored.
Sample table: goal_details
goal_id | match_no | player_id | team_id | goal_time | goal_type | play_stage | goal_schedule | goal_half
---------+----------+-----------+---------+-----------+-----------+------------+---------------+-----------
1 | 1 | 160159 | 1207 | 57 | N | G | NT | 2
2 | 1 | 160368 | 1216 | 65 | P | G | NT | 2
3 | 1 | 160154 | 1207 | 89 | N | G | NT | 2
4 | 2 | 160470 | 1221 | 5 | N | G | NT | 1
5 | 3 | 160547 | 1224 | 10 | N | G | NT | 1
6 | 3 | 160403 | 1218 | 61 | N | G | NT | 2
7 | 3 | 160550 | 1224 | 81 | N | G | NT | 2
8 | 4 | 160128 | 1206 | 73 | N | G | NT | 2
9 | 4 | 160373 | 1217 | 93 | N | G | ST | 2
10 | 5 | 160084 | 1204 | 41 | N | G | NT | 1
11 | 6 | 160298 | 1213 | 51 | N | G | NT | 2
12 | 7 | 160183 | 1208 | 19 | N | G | NT | 1
13 | 7 | 160180 | 1208 | 93 | N | G | ST | 2
14 | 8 | 160423 | 1219 | 87 | N | G | NT | 2
15 | 9 | 160335 | 1215 | 48 | N | G | NT | 2
16 | 9 | 160327 | 1215 | 71 | O | G | NT | 2
17 | 10 | 160244 | 1211 | 32 | N | G | NT | 1
18 | 10 | 160252 | 1211 | 93 | N | G | ST | 2
19 | 11 | 160207 | 1209 | 62 | N | G | NT | 2
20 | 11 | 160200 | 1209 | 87 | N | G | NT | 2
21 | 12 | 160320 | 1214 | 31 | N | G | NT | 1
22 | 12 | 160221 | 1210 | 50 | N | G | NT | 2
23 | 13 | 160411 | 1218 | 32 | N | G | NT | 1
24 | 13 | 160405 | 1218 | 45 | N | G | NT | 1
25 | 13 | 160380 | 1217 | 80 | N | G | NT | 2
26 | 14 | 160368 | 1216 | 18 | P | G | NT | 1
27 | 14 | 160481 | 1221 | 57 | N | G | NT | 2
28 | 15 | 160160 | 1207 | 90 | N | G | NT | 2
29 | 15 | 160154 | 1207 | 96 | N | G | ST | 2
30 | 16 | 160547 | 1224 | 42 | N | G | NT | 1
31 | 16 | 160138 | 1206 | 56 | N | G | NT | 2
32 | 16 | 160137 | 1206 | 93 | N | G | ST | 2
33 | 17 | 160262 | 1212 | 49 | N | G | NT | 2
34 | 17 | 160275 | 1212 | 96 | N | G | ST | 2
35 | 19 | 160248 | 1211 | 88 | N | G | NT | 2
36 | 20 | 160085 | 1204 | 37 | N | G | NT | 1
37 | 20 | 160086 | 1204 | 59 | N | G | NT | 2
38 | 20 | 160115 | 1205 | 76 | N | G | NT | 2
39 | 20 | 160114 | 1205 | 89 | P | G | NT | 2
40 | 21 | 160435 | 1219 | 34 | N | G | NT | 1
41 | 21 | 160436 | 1219 | 37 | N | G | NT | 1
42 | 21 | 160435 | 1219 | 48 | N | G | NT | 2
43 | 22 | 160067 | 1203 | 48 | N | G | NT | 2
44 | 22 | 160064 | 1203 | 61 | N | G | NT | 2
45 | 22 | 160067 | 1203 | 70 | N | G | NT | 2
46 | 23 | 160224 | 1210 | 40 | P | G | NT | 1
47 | 23 | 160216 | 1210 | 88 | O | G | NT | 2
48 | 25 | 160023 | 1201 | 43 | N | G | NT | 1
49 | 27 | 160544 | 1224 | 11 | N | G | NT | 1
50 | 27 | 160538 | 1224 | 20 | N | G | NT | 1
51 | 27 | 160547 | 1224 | 67 | N | G | NT | 2
52 | 29 | 160287 | 1213 | 54 | N | G | NT | 2
53 | 30 | 160182 | 1208 | 30 | N | G | NT | 1
54 | 31 | 160504 | 1222 | 10 | N | G | NT | 1
55 | 31 | 160500 | 1222 | 65 | N | G | NT | 2
56 | 32 | 160435 | 1219 | 7 | N | G | NT | 1
57 | 32 | 160089 | 1204 | 45 | N | G | NT | 1
58 | 32 | 160085 | 1204 | 87 | N | G | NT | 2
59 | 33 | 160226 | 1210 | 18 | N | G | NT | 1
60 | 33 | 160042 | 1202 | 60 | N | G | NT | 2
61 | 33 | 160226 | 1210 | 94 | N | G | ST | 2
62 | 34 | 160203 | 1209 | 19 | N | G | NT | 1
63 | 34 | 160320 | 1214 | 42 | N | G | NT | 1
64 | 34 | 160202 | 1209 | 47 | N | G | NT | 2
65 | 34 | 160322 | 1214 | 50 | N | G | NT | 2
66 | 34 | 160202 | 1209 | 55 | N | G | NT | 2
67 | 34 | 160322 | 1214 | 62 | N | G | NT | 2
68 | 35 | 160333 | 1215 | 85 | N | G | NT | 2
69 | 36 | 160063 | 1203 | 84 | N | G | NT | 2
70 | 37 | 160287 | 1213 | 39 | N | R | NT | 1
71 | 37 | 160476 | 1221 | 82 | N | R | NT | 2
72 | 38 | 160262 | 1212 | 75 | O | R | NT | 2
73 | 39 | 160321 | 1214 | 117 | N | R | ET | 2
74 | 40 | 160333 | 1215 | 2 | P | R | NT | 1
75 | 40 | 160160 | 1207 | 58 | N | R | NT | 2
76 | 40 | 160160 | 1207 | 61 | N | R | NT | 2
77 | 41 | 160165 | 1208 | 8 | N | R | NT | 1
78 | 41 | 160182 | 1208 | 43 | N | R | NT | 1
79 | 41 | 160173 | 1208 | 63 | N | R | NT | 2
80 | 42 | 160050 | 1203 | 10 | N | R | NT | 1
81 | 42 | 160065 | 1203 | 78 | N | R | NT | 2
82 | 42 | 160062 | 1203 | 80 | N | R | NT | 2
83 | 42 | 160058 | 1203 | 90 | N | R | NT | 2
84 | 43 | 160236 | 1211 | 33 | N | R | NT | 1
85 | 43 | 160252 | 1211 | 91 | N | R | ST | 2
86 | 44 | 160136 | 1206 | 4 | P | R | NT | 1
87 | 44 | 160219 | 1210 | 6 | N | R | NT | 1
88 | 44 | 160230 | 1210 | 18 | N | R | NT | 1
89 | 45 | 160297 | 1213 | 2 | N | Q | NT | 1
90 | 45 | 160316 | 1214 | 33 | N | Q | NT | 1
91 | 46 | 160063 | 1203 | 13 | N | Q | NT | 1
92 | 46 | 160539 | 1224 | 31 | N | Q | NT | 1
93 | 46 | 160550 | 1224 | 55 | N | Q | NT | 2
94 | 46 | 160551 | 1224 | 86 | N | Q | NT | 2
95 | 47 | 160177 | 1208 | 65 | N | Q | NT | 2
96 | 47 | 160235 | 1211 | 78 | P | Q | NT | 2
97 | 48 | 160159 | 1207 | 12 | N | Q | NT | 1
98 | 48 | 160155 | 1207 | 20 | N | Q | NT | 1
99 | 48 | 160154 | 1207 | 43 | N | Q | NT | 1
100 | 48 | 160160 | 1207 | 45 | N | Q | NT | 1
101 | 48 | 160230 | 1210 | 56 | N | Q | NT | 2
102 | 48 | 160159 | 1207 | 59 | N | Q | NT | 2
103 | 48 | 160221 | 1210 | 84 | N | Q | NT | 2
104 | 49 | 160322 | 1214 | 50 | N | S | NT | 2
105 | 49 | 160320 | 1214 | 53 | N | S | NT | 2
106 | 50 | 160160 | 1207 | 47 | P | S | ST | 1
107 | 50 | 160160 | 1207 | 72 | N | S | NT | 2
108 | 51 | 160319 | 1214 | 109 | N | F | ET | 2
Sample Solution:
-- This SQL query retrieves the count of goals for each match, grouping the results by 'match_no' and ordering them in ascending order.
SELECT match_no, COUNT(*)
-- Selects the 'match_no' column and the count of rows for each group.
FROM goal_details
-- 'goal_details' is the name of the table being queried.
GROUP BY match_no
-- Groups the results by the 'match_no' column.
ORDER BY match_no;
-- Orders the result set based on the 'match_no' column in ascending order.
Sample Output:
match_no | count
----------+-------
1 | 3
2 | 1
3 | 3
4 | 2
5 | 1
6 | 1
7 | 2
8 | 1
9 | 2
10 | 2
11 | 2
12 | 2
13 | 3
14 | 2
15 | 2
16 | 3
17 | 2
19 | 1
20 | 4
21 | 3
22 | 3
23 | 2
25 | 1
27 | 3
29 | 1
30 | 1
31 | 2
32 | 3
33 | 3
34 | 6
35 | 1
36 | 1
37 | 2
38 | 1
39 | 1
40 | 3
41 | 3
42 | 4
43 | 2
44 | 3
45 | 2
46 | 4
47 | 2
48 | 7
49 | 2
50 | 2
51 | 1
(47 rows)
Code Explanation:
The said query in SQL that selects the "match_no" and the count of rows from the 'goal_details' table, grouped by the "match_no" column, and sorted the result set in ascending order based on the "match_no" column.
The "GROUP BY" clause groups the rows in the 'goal_details' table by the "match_no" column. "COUNT" is a function that counts the number of rows in each of the groups. As a result of the "ORDER BY" clause, the groups are sorted in ascending order based upon the "match_no" column within the group.
Relational Algebra Expression:
Relational Algebra Tree:
Practice Online
Sample Database: soccer
Query Visualization:
Duration:
Rows:
Cost:
Have another way to solve this solution? Contribute your code (and comments) through Disqus.
Previous SQL Exercise: Matches decided by penalties in the Round of 16.
Next SQL Exercise: Matches, first half with no stoppage time added.
What is the difficulty level of this exercise?
Test your Programming skills with w3resource's quiz.