# SQL Exercise: Find the number of shots scored in penalty shootouts

## SQL soccer Database: Exercise-23 with Solution

**23.** From the following table, write a SQL query to count the number of shots that were scored in penalty shootouts matches. Return number of shots scored goal as "Goal Scored by Penalty Kicks".

*Sample table:*penalty_shootout

kick_id | match_no | team_id | player_id | score_goal | kick_no ---------+----------+---------+-----------+------------+--------- 1 | 37 | 1221 | 160467 | Y | 1 2 | 37 | 1213 | 160297 | Y | 2 3 | 37 | 1221 | 160477 | N | 3 4 | 37 | 1213 | 160298 | Y | 4 5 | 37 | 1221 | 160476 | Y | 5 6 | 37 | 1213 | 160281 | Y | 6 7 | 37 | 1221 | 160470 | Y | 7 8 | 37 | 1213 | 160287 | Y | 8 9 | 37 | 1221 | 160469 | Y | 9 10 | 37 | 1213 | 160291 | Y | 10 11 | 45 | 1214 | 160322 | Y | 1 12 | 45 | 1213 | 160297 | Y | 2 13 | 45 | 1214 | 160316 | Y | 3 14 | 45 | 1213 | 160298 | Y | 4 15 | 45 | 1214 | 160314 | Y | 5 16 | 45 | 1213 | 160281 | Y | 6 17 | 45 | 1214 | 160320 | Y | 7 18 | 45 | 1213 | 160287 | N | 8 19 | 45 | 1214 | 160321 | Y | 9 20 | 47 | 1211 | 160251 | Y | 1 21 | 47 | 1208 | 160176 | Y | 2 22 | 47 | 1211 | 160253 | N | 3 23 | 47 | 1208 | 160183 | N | 4 24 | 47 | 1211 | 160234 | Y | 5 25 | 47 | 1208 | 160177 | N | 6 26 | 47 | 1211 | 160252 | N | 7 27 | 47 | 1208 | 160173 | Y | 8 28 | 47 | 1211 | 160235 | N | 9 29 | 47 | 1208 | 160180 | N | 10 30 | 47 | 1211 | 160244 | Y | 11 31 | 47 | 1208 | 160168 | Y | 12 32 | 47 | 1211 | 160246 | Y | 13 33 | 47 | 1208 | 160169 | Y | 14 34 | 47 | 1211 | 160238 | Y | 15 35 | 47 | 1208 | 160165 | Y | 16 36 | 47 | 1211 | 160237 | N | 17 37 | 47 | 1208 | 160166 | Y | 18

**Sample Solution:**

**SQL Code:**

```
-- This SQL query calculates the count of rows in the 'penalty_shootout' table
-- where the 'score_goal' column has the value 'Y', and aliases the result as "Goal Scored by Penalty Kicks".
SELECT COUNT(*) AS "Goal Scored by Penalty Kicks"
-- COUNT(*) is an aggregate function that counts the number of rows in a result set, and the alias is set to "Goal Scored by Penalty Kicks".
FROM penalty_shootout
-- 'penalty_shootout' is the name of the table being queried.
WHERE score_goal='Y';
-- The WHERE clause filters rows where the 'score_goal' column has the value 'Y'.
```

Sample Output:

Goal Scored by Penalty Kicks ------------------------------ 28 (1 row)

**Code Explanation:**

The said query in SQL that counts the number of goals scored by penalty kicks from the table 'penalty_shootout' and aliases the result column as "Goal Scored by Penalty Kicks". The WHERE clause in the query will consider the total number of penalty kicks resulting in a goal being scored.

## 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 shots taken in penalty shootout matches.

**Next SQL Exercise:** Number of shots missed or saved in penalty shootouts.

**What is the difficulty level of this exercise?**

Test your Programming skills with w3resource's quiz.

**Weekly Trends**- 100 Python Projects for Beginners with solution
- Mastering NumPy: 100 Exercises with solutions
- SQL Tutorial
- SQL Inner Join
- Python Exercises, Practice, Solution
- Python Interview Questions and Answers: Comprehensive Guide
- MongoDB Exercises, Practice, Solution
- SQL Exercises, Practice, Solution - JOINS
- Java Basic Programming Exercises
- SQL Subqueries
- C# Sharp Basic Exercises
- SQL COUNT() with distinct
- JavaScript HTML Form Validation
- Java Collection Exercises