Querying the Team names with more than a half wins (Database contains Home and Away Column for results)

soccer database queries
you are given two tables teams and matches codility
sql
sql query to retrieve data from database
query to schedule a match between each team
sql queries examples with answers
sql to rank teams based on the matches
sample employee table sql with data

I have a table (Meetings) which contains the following columns:

  • HomeTeam(varchar)
  • AwayTeam(varchar)
  • Home(int)
  • Away(int)

For the scores (Example: 3:2).

My problem is that I need to output all teams which have more than a half wins.

The code provided is working perfect, but it is only for the Home Wins and I need a sum of both Home and Away, because for example, 'Liverpool' has three Home games and two Away games and I need from both sides scores.

SELECT Team, COUNT(*) AS HomeWins
FROM Teams
JOIN Meetings ON Team = Meetings.HomeTeam AND Meetings.Home > Meetings.Away
GROUP BY Team

The expected output is (from 6 teams I have two teams which have 3 wins and the other one - 4) to output the teams with more than a half wins and their wins total.

Example:

Team       Wins
Liverpool  4
PSG        3

If you just want wins, you can use:

select t.*,
       (select count(*)
        from meetings m
        where (m.hometeam = t.team and m.home > m.away) or
              (m.awayteam = t.team and m.away > m.away)
       ) as num_wins
from team t;

Note: This returns all teams, even those who have not won any games.

SQL exercises on soccer Database, Practice with solution of exercises on SQL soccer database, INNER JOIN, NON EQUI JOIN, subqueries on soccer database, and more from w3resource. column of soccer_country table; team_group – the name of the group in decided_by – how the result of the match has been decided, either N for by  Data is being gathered and created at rates unprecedented in history. Much of this data is intended to drive business outcomes but, according to the Harvard Business Review, “…on average, less than half of an organization’s structured data is actively used in making decisions…” The root of the problem is that data is typically spread […]

You just need to query the results twice, inverting the query and using UNION to create a combined view.

create table #meetings (HomeTeam varchar(20), AwayTeam varchar(20), Home int, Away int);


insert into #meetings (HomeTeam, AwayTeam, Home, Away)
values
    ('Trumpton', 'Camberwick Green', 1, 0), -- Trumpton win
    ('Chigley', 'Camberwick Green', 3, 2), -- Chigley win
    ('Trumpton', 'Chigley', 3, 1), -- Trumpton win
    ('Camberwick Green', 'Trumpton', 0, 2), -- Trumpton win
    ('Camberwick Green', 'Chigley', 1, 2) -- Chigley win

/*
Trumpton - played 3, won 3
Camberwick Green - played 4, won 0
Chigley - played 3, won 2
*/

select
    Team
    ,count(*) as Played
    ,sum(Win) as Wins
from
    (
    select
        HomeTeam as Team
        ,case when Home > Away then 1 else 0 end as Win
    from
        #meetings
    union all
    select
        AwayTeam as Team
        ,case when Away > Home then 1 else 0 end as Win
    from
        #meetings
    ) results

Importing Data from a Database Using Python, Finally, teams are described by their name, conference, current conference rank, home wins and losses, and away wins and losses. Of course  Now add a new column by clicking where the column is to appear and choose Insert, Column. In the Field area of this new column type: Name: [FirstName] & " " & [LastName] This creates a new field in the query results window called Name and includes in it the contents of the FirstName field, a space and the contents of the LastName field.

From what I understand, you want to return info for teams that have more wins than losses. If that's correct here is one way to do it:

-- Build cte to re-use query
WITH meetings(WinningTeam, LosingTeam) AS (
  SELECT
    CASE WHEN Home > Away THEN HomeTeam ELSE AwayTeam END AS WinningTeam,
    CASE WHEN Home > Away THEN AwayTeam ELSE HomeTeam END AS LosingTeam
  FROM Meetings
)
SELECT teams.Team, wins.win_count AS Wins
FROM teams
LEFT JOIN (
  SELECT WinningTeam, COUNT(*) AS win_count
  FROM Meetings
  GROUP BY 1
) wins ON teams.team = wins.WinningTeam -- Get wins per team
LEFT JOIN (
  SELECT LosingTeam, count(*) AS loss_count
  FROM Meetings
  GROUP BY 1
) losses ON teams.team = wins.LosingTeam -- Get wins per team
WHERE win_count > loss_count -- Only return teams with winning records

Backpacker, I study the descriptions of product attributes up front, underlining key portions and circling my ideal features. Next, I make a list of all the attributes and features my ideal tent should contain. The results of my tent search were mixed. If I'm spending money on gear I may use for a decade or more, I want my information from  We're changing our Database Administration team from a group of 3 DBAs to a group of 3 DBAs plus a BI person plus a Data Steward. Since BI people and Data Stewards don't tend to do DBA tasks, my boss is looking for a new team name, something that includes all data-related jobs, to add to the organization chart.

The Best SQL Examples, This is used to select the database containing the tables for your SQL statements: to determine which columns of the data you want to show in the results. Order By gives us a way to sort the result set by one or more of the items in Here is the same list as above, but sorted by the students Full Name. Queries or references using three- or four-part names not supported in Azure SQL Database. Three-part name format, [database_name].[schema_name].[object_name], is supported only when the database_name is the current database or the database_name is tempdb and the object_name starts with #.

Converting Rows to Columns and Back Again: Databases for , _datacamp <3 sql as result; Filter the query for FC Schalke 04 and FC Bayern Munich using IN , giving you SELECT -- Select the date of the match date, -- Identify home wins, losses, This allows us to retrieve the away team's identity. SELECT m.date, --Select the team long name column and call it  You always specify columns by name. The only place where it is okay to "SELECT *" is an ad-hoc query on the command line. Three extremely powerful reasons for that

42 Questions on SQL for all aspiring Data Scientists, It uses this table of match results from a simple league: The pivot clause, introduced in Oracle Database 11g, allows you to do the same more easily. The column containing values you want to become new columns. Complete this query to show the team names as columns, with the number of home  We also tried querying both contexts to get the data we needed. For example, our business logic would query half of what it needed from context 1 and the other half from context 2. This had some major issues. Instead of performing one query against a single context, we had to perform multiple queries across different contexts.

Comments
  • See Why should I provide a Minimal Reproducible Example for a very simple SQL query? ... Also you need to tag a RDMS product, the sql tag on this website does not mean a RDMS product but the ANSI/ISO SQL standard..
  • Tag your question with the database you are using.
  • Thanks man, this works perfectly, but I need to filter the teams which from 5 games have less than 3 won games. For example: Barcelona 1W, Bayern 3W, Juventus 1W, Liverpool 4W, ManUnited 2W, PSG 3W. I want to get only Bayern, Liverpool and PSG.
  • @S1C4R10 . . . I would suggest that you ask a new question, which is more specific on your actual needs.