How do I include duplicates? Tried HAVING

Taylar Massengale 0
Viewed 43

I am trying to learn SQL, and I am doing a project based on a provided database about past Superbowls. I wrote the below code to try to return a "yes" or "no" (to show I know how to use CASE) in a new column for teams that beat their opponents by more than 14 points. It worked, in a sense, but only returned each winning team once, AKA removed duplicates for teams that have won multiple times, but I want it to return all duplicates to show all games, HELP! I tried a HAVING clause, but I didn't really know what to put...

display which teams have beaten their opponents by >=14 points

I have Tried this below query:

SELECT Winner, Winner_Pts, Loser, Loser_Pts,Date,
    CASE
        WHEN (AVG(Winner_Pts-Loser_Pts) >= 14) THEN "yes"
        ELSE "no"
        END as "won_by_more_than_14"
FROM superbowls
GROUP BY Winner
ORDER BY Winner_Pts DESC
having duplicates sql
1 Answers

In your scenario, there is no need to aggregate your data in order to find teams that have beaten their opponents by >= 14 points.

If you remove your AVG function and GROUP BY aggregation, you will return teams that have won the super bowl (and more than once); otherwise, your CASE statement is correct.

SELECT Winner, 
       Winner_Pts, 
       Loser, 
       Loser_Pts,
       Date,
       CASE 
          WHEN (Winner_Pts-Loser_Pts) >= 14 THEN "Yes"
          ELSE "No"
       END AS "won_by_more_than_14"
FROM superbowls
ORDER BY Winner_Pts DESC

You can even add your CASE statement to a WHERE clause to only SELECT rows for teams than won by more than 14 points.

WHERE (CASE 
          WHEN (Winner_Pts-Loser_Pts) >= 14 THEN "Yes"
          ELSE "No"
       END) = "Yes"

Input Data:

ID Number Winner Winner_Pts Loser Loser_Pts Date
1 LVI Rams 23 Bengals 20 2022-02-13 00:00:00
2 LV Buccaneers 31 Chiefs 9 2021-02-07 00:00:00
3 LVI Chiefs 31 49ers 20 2022-02-02 00:00:00
4 LII Eagles 41 Patriots 33 2018-02-04 00:00:00
5 50 Broncos 24 Panthers 10 2016-02-07 00:00:00
6 XLVIII Seahawks 43 Denver 8 2014-02-02 00:00:00
7 XXXIV Rams 23 Titans 16 2000-01-30 00:00:00
8 LIII Patriots 13 Rams 3 2019-02-03 00:00:00
9 LI Patriots 34 Falcons 28 2017-02-05 00:00:00

Output Data:

Winner Winner_Pts Loser Loser_Pts Date won_by_more_than_14
Seahawks 43 Denver 8 2014-02-02 00:00:00 Yes
Eagles 41 Patriots 33 2018-02-04 00:00:00 No
Patriots 34 Falcons 28 2017-02-05 00:00:00 No
Buccaneers 31 Chiefs 9 2021-02-07 00:00:00 Yes
Chiefs 31 49ers 20 2022-02-02 00:00:00 No
Broncos 24 Panthers 10 2016-02-07 00:00:00 Yes
Rams 23 Bengals 20 2022-02-13 00:00:00 No
Rams 23 Titans 16 2000-01-30 00:00:00 No
Patriots 13 Rams 3 2019-02-03 00:00:00 No

See Fiddle here.

Details:

Removing the AVG() function will get the results you want, but that doesn't mean AVG() isn't useful, especially for sports data. If you did want to aggregate your data, please see the following:

The AVG() function is used to find the average of values over records from a table. AVG() belongs to a class of functions known as aggregate functions. An aggregate function returns a single computed result over multiple rows:

Aggregate Function Example Use Case
SUM() Find the sum of points by team.
COUNT() Find the number of bowls by each team.
MAX() Find the highest point value by each team.
MIN() Find the lowest point value by each team.
AVG() Find the average points by team.

The SQL GROUP BY clause is used to group rows together. In most cases, a GROUP BY clause has one or more aggregate functions that calculate one or more metrics for the group.

Let's take this example here, I'm simply returning all Winners and their points:

SELECT 
  Winner,
  Winner_Pts AS 'points'
FROM superbowls
ORDER BY Winner_Pts DESC
Winner points
Seahawks 43
Eagles 41
Patriots 34
Buccaneers 31
Chiefs 31
Broncos 24
Rams 23
Rams 23
Patriots 13

Now let's aggregate it by Winner to find the average points:

SELECT 
  Winner,
  ROUND(AVG(Winner_Pts)) AS 'avg_points'
FROM superbowls
GROUP BY Winner
ORDER BY ROUND(AVG(Winner_Pts)) DESC
Winner avg_points
Seahawks 43
Eagles 41
Buccaneers 31
Chiefs 31
Broncos 24
Patriots 24
Rams 23

As you can see between the two queries above, the Rams and Patriots only have a single row (GROUPED BY Winner), and the average is:

(23+23)/2 = 23 (Rams)

(13+34)/2 = 23.5 - Rounded to 24 (Patriots)

(41)/1 = 41 (Eagles)

Source.

If you want to filter your data using GROUP BY, use HAVING. This is different from the WHERE clause because the GROUP BY clause runs after WHERE clauses which means that you can only use WHERE on “raw” data and not on aggregated values. You need to use HAVING on aggregated metrics.

The primary use of the HAVING operation is to filter aggregated data. You can use it when you summarize your data with GROUP BY into new metrics, and you want to select the results based on these new values.

Example:

Find teams with average winning points greater than 40:

SELECT 
  Winner,
  ROUND(AVG(Winner_Pts)) AS 'avg_points'
FROM superbowls
GROUP BY Winner
HAVING ROUND(AVG(Winner_Pts)) > 40
ORDER BY ROUND(AVG(Winner_Pts)) DESC
Winner avg_points
Seahawks 43
Eagles 41

Source.

GRIV0
Related