Group by demands us to include all selected rows, when we need the results grouped by just one row

Question

Following this programming exercise: SQL with Street Fighter, which statement is:

It's time to assess which of the world's greatest fighters are through to the 6 coveted places in the semi-finals of the Street Fighter World Fighting Championship. Every fight of the year has been recorded and each fighter's wins and losses need to be added up.

Each row of the table fighters records, alongside the fighter's name, whether they won (1) or lost (0), as well as the type of move that ended the bout.

id
name
won
lost
move_id

winning_moves

id
move

However, due to new health and safety regulations, all ki blasts have been outlawed as a potential fire hazard. Any bout that ended with Hadoken, Shouoken or Kikoken should not be counted in the total wins and losses.

So, your job:

Return name, won, and lost columns displaying the name, total number of wins and total number of losses. Group by the fighter's

name. Do not count any wins or losses where the winning move was Hadoken, Shouoken or Kikoken. Order from most-wins to least Return the top 6. Don't worry about ties.

How could we group the fighters by their names?

We have tried:

select name, won, lost from fighters inner join winning_moves on fighters.id=winning_moves.id

group by name order by won desc limit 6;

However it displays:

There was an error with the SQL query:

PG::GroupingError: ERROR: column "fighters.won" must appear in the GROUP BY clause or be used in an aggregate function LINE 3: select name, won, lost from fighters inner join winning_move...

In addition we have also tried to include all selected rows:

select name, won, lost from fighters inner join winning_moves on fighters.id=winning_moves.id
group by name,won,lost order by won desc limit 6;

But the results differ from the expected.

Expected:

name    won lost
Sakura  44  15
Cammy   44  17
Rose    42  19
Karin   42  13
Dhalsim 40  15
Ryu 39  16

Actual:

name    won lost
Vega    2   1
Guile   2   1
Ryu 2   1
Rose    1   0
Vega    1   0
Zangief 1   0

Besides we have read:

• https://www.w3schools.com/sql/sql_join.asp
• MySql Inner Join with WHERE clause
• How to limit rows in PostgreSQL SELECT
• https://www.w3schools.com/sql/sql_groupby.asp
• GROUP BY clause or be used in an aggregate function
• PostgreSQL column must appear in the GROUP BY clause or be used in an aggregate function when using case statement
• must appear in the GROUP BY clause or be used in an aggregate function

Answer 1

I guess you need to have sum() to aggregate the ids wins n loss. In addition to that you dont need join as you dont wanna show the move in the first query

     select name, sum(won) as wins, 
     sum(lost) 
     from fighters 
    group by name order by sum(won) 
   desc limit 6;