SQL Exercise: Number of goals scored during a normal play schedule

Last update on January 13 2026 12:20:28 (UTC/GMT +8 hours)

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
.........
     108 |       51 |    160319 |    1214 |       109 | N         | F          | ET            |         2

View the table

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: