SQL Exercise: Find the number of matches ended with a result
SQL soccer Database: Basic Exercise-4 with Solution
4. From the following table, write a SQL query to find the number of matches that ended with a result.
Sample table: match_mast
match_no | play_stage | play_date | results | decided_by | goal_score | venue_id | referee_id | audence | plr_of_match | stop1_sec | stop2_sec
----------+------------+------------+---------+------------+------------+----------+------------+---------+--------------+-----------+-----------
1 | G | 2016-06-11 | WIN | N | 2-1 | 20008 | 70007 | 75113 | 160154 | 131 | 242
2 | G | 2016-06-11 | WIN | N | 0-1 | 20002 | 70012 | 33805 | 160476 | 61 | 182
3 | G | 2016-06-11 | WIN | N | 2-1 | 20001 | 70017 | 37831 | 160540 | 64 | 268
4 | G | 2016-06-12 | DRAW | N | 1-1 | 20005 | 70011 | 62343 | 160128 | 0 | 185
5 | G | 2016-06-12 | WIN | N | 0-1 | 20007 | 70006 | 43842 | 160084 | 125 | 325
6 | G | 2016-06-12 | WIN | N | 1-0 | 20006 | 70014 | 33742 | 160291 | 2 | 246
7 | G | 2016-06-13 | WIN | N | 2-0 | 20003 | 70002 | 43035 | 160176 | 89 | 188
8 | G | 2016-06-13 | WIN | N | 1-0 | 20010 | 70009 | 29400 | 160429 | 360 | 182
9 | G | 2016-06-13 | DRAW | N | 1-1 | 20008 | 70010 | 73419 | 160335 | 67 | 194
10 | G | 2016-06-14 | WIN | N | 0-2 | 20004 | 70005 | 55408 | 160244 | 63 | 189
11 | G | 2016-06-14 | WIN | N | 0-2 | 20001 | 70018 | 34424 | 160197 | 61 | 305
12 | G | 2016-06-15 | DRAW | N | 1-1 | 20009 | 70004 | 38742 | 160320 | 15 | 284
13 | G | 2016-06-15 | WIN | N | 1-2 | 20003 | 70001 | 38989 | 160405 | 62 | 189
14 | G | 2016-06-15 | DRAW | N | 1-1 | 20007 | 70015 | 43576 | 160477 | 74 | 206
15 | G | 2016-06-16 | WIN | N | 2-0 | 20005 | 70013 | 63670 | 160154 | 71 | 374
16 | G | 2016-06-16 | WIN | N | 2-1 | 20002 | 70003 | 34033 | 160540 | 62 | 212
17 | G | 2016-06-16 | WIN | N | 0-2 | 20004 | 70016 | 51043 | 160262 | 7 | 411
18 | G | 2016-06-17 | DRAW | N | 0-0 | 20008 | 70008 | 73648 | 160165 | 6 | 208
19 | G | 2016-06-17 | WIN | N | 1-0 | 20010 | 70007 | 29600 | 160248 | 2 | 264
20 | G | 2016-06-17 | DRAW | N | 2-2 | 20009 | 70005 | 38376 | 160086 | 71 | 280
21 | G | 2016-06-18 | WIN | N | 3-0 | 20006 | 70010 | 33409 | 160429 | 84 | 120
22 | G | 2016-06-18 | WIN | N | 3-0 | 20001 | 70004 | 39493 | 160064 | 11 | 180
23 | G | 2016-06-18 | DRAW | N | 1-1 | 20005 | 70015 | 60842 | 160230 | 61 | 280
24 | G | 2016-06-19 | DRAW | N | 0-0 | 20007 | 70011 | 44291 | 160314 | 3 | 200
25 | G | 2016-06-20 | WIN | N | 0-1 | 20004 | 70016 | 49752 | 160005 | 125 | 328
26 | G | 2016-06-20 | DRAW | N | 0-0 | 20003 | 70001 | 45616 | 160463 | 60 | 122
27 | G | 2016-06-21 | WIN | N | 0-3 | 20010 | 70006 | 28840 | 160544 | 62 | 119
28 | G | 2016-06-21 | DRAW | N | 0-0 | 20009 | 70012 | 39051 | 160392 | 62 | 301
29 | G | 2016-06-21 | WIN | N | 0-1 | 20005 | 70017 | 58874 | 160520 | 29 | 244
30 | G | 2016-06-21 | WIN | N | 0-1 | 20007 | 70018 | 44125 | 160177 | 21 | 195
31 | G | 2016-06-22 | WIN | N | 0-2 | 20002 | 70013 | 32836 | 160504 | 60 | 300
32 | G | 2016-06-22 | WIN | N | 2-1 | 20001 | 70008 | 37245 | 160085 | 70 | 282
33 | G | 2016-06-22 | WIN | N | 2-1 | 20008 | 70009 | 68714 | 160220 | 7 | 244
34 | G | 2016-06-22 | DRAW | N | 3-3 | 20004 | 70002 | 55514 | 160322 | 70 | 185
35 | G | 2016-06-23 | WIN | N | 0-1 | 20003 | 70014 | 44268 | 160333 | 79 | 221
36 | G | 2016-06-23 | WIN | N | 0-1 | 20006 | 70003 | 34011 | 160062 | 63 | 195
37 | R | 2016-06-25 | WIN | P | 1-1 | 20009 | 70005 | 38842 | 160476 | 126 | 243
38 | R | 2016-06-25 | WIN | N | 1-0 | 20007 | 70002 | 44342 | 160547 | 5 | 245
39 | R | 2016-06-26 | WIN | N | 0-1 | 20002 | 70012 | 33523 | 160316 | 61 | 198
40 | R | 2016-06-26 | WIN | N | 2-1 | 20004 | 70011 | 56279 | 160160 | 238 | 203
41 | R | 2016-06-26 | WIN | N | 3-0 | 20003 | 70009 | 44312 | 160173 | 62 | 124
42 | R | 2016-06-27 | WIN | N | 0-4 | 20010 | 70010 | 28921 | 160062 | 3 | 133
43 | R | 2016-06-27 | WIN | N | 2-0 | 20008 | 70004 | 76165 | 160235 | 63 | 243
44 | R | 2016-06-28 | WIN | N | 1-2 | 20006 | 70001 | 33901 | 160217 | 5 | 199
45 | Q | 2016-07-01 | WIN | P | 1-1 | 20005 | 70003 | 62940 | 160316 | 58 | 181
46 | Q | 2016-07-02 | WIN | N | 3-1 | 20003 | 70001 | 45936 | 160550 | 14 | 182
47 | Q | 2016-07-03 | WIN | P | 1-1 | 20001 | 70007 | 38764 | 160163 | 63 | 181
48 | Q | 2016-07-04 | WIN | N | 5-2 | 20008 | 70008 | 76833 | 160159 | 16 | 125
49 | S | 2016-07-07 | WIN | N | 2-0 | 20004 | 70006 | 55679 | 160322 | 2 | 181
50 | S | 2016-07-08 | WIN | N | 2-0 | 20005 | 70011 | 64078 | 160160 | 126 | 275
51 | F | 2016-07-11 | WIN | N | 1-0 | 20008 | 70005 | 75868 | 160307 | 161 | 181
Sample Solution:
-- This SQL query calculates the count of rows in the 'match_mast' table where the 'results' column has the value 'WIN'.
SELECT COUNT(*)
-- COUNT(*) is an aggregate function that counts the number of rows in a result set.
FROM match_mast
-- 'match_mast' is the name of the table being queried.
WHERE results='WIN';
-- The WHERE clause filters rows where the 'results' column has the value 'WIN'.
Sample Output:
count
-------
40
(1 row)
Code Explanation:
The said query in SQL which will count the total number of matches in the 'match_mast' table where the value of the "results" column is "WIN".
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: Number of goals in EURO cup 2016 in normal schedule.
Next SQL Exercise: Find the number of matches ended with draws.
What is the difficulty level of this exercise?
Test your Programming skills with w3resource's quiz.
It will be nice if you may share this link in any developer community or anywhere else, from where other developers may find this content. Thanks.
https://www.w3resource.com/sql-exercises/soccer-database-exercise/sql-basic-exercise-soccer-database-4.php
- Weekly Trends and Language Statistics
- Weekly Trends and Language Statistics