CODEWITHSUNDEEP
sqlsqlite
s878
s878

Reputation: 57

how to Subtract 2 Columns in SQLite?

If given the table:

games_played(ID,home_team,away_team,homescore,awayscore)

inputs:

('1', 'USA', 'CAN', '0','2')

('2', 'USA', 'CAN', '1','2')

('3', 'USA', 'CAN', '3','0')

('4', 'SWE', 'CAN', '3','2')

('5', 'CAN', 'RUS', '3','2')

('6', 'DE', 'RUS', '0','2')

Output should be the teams: SWE, CAN

What would I do to find team with more home wins than losses? I've tried doing this:

select home_team, count(*)
  from games_played
  where homescore > awayscore
  group by home_team

-

select home_team, count(*)
  from games_played
  where awayscore > homescore
  group by home_team

The first query should return the team name and the number of games they won at home. The second should be the number of games they lost at home. This query doesn't work and I'm looking for another way. I know that I cant use count in 'where'

Upvotes: 2

Views: 844

Answers (2)

Brien Foss
Brien Foss

Reputation: 3367

Use conditional aggregation:

SELECT home_team,
COUNT(CASE WHEN homescore > awayscore THEN 1 END) AS gamesWon,
COUNT(CASE WHEN awayscore > homescore THEN 1 END) AS gamesLost
FROM games_played
GROUP BY home_team

Result:

enter image description here

Now to further return only teams where gamesWon > gamesLost you put it into a common table expression:

WITH cte AS (
SELECT home_team,
COUNT(CASE WHEN homescore > awayscore THEN 1 END) AS gamesWon,
COUNT(CASE WHEN awayscore > homescore THEN 1 END) AS gamesLost
FROM games_played
GROUP BY home_team)

SELECT * FROM cte
WHERE gamesWon > gamesLost

Result:

enter image description here

Repro:

[DEMO HERE]

CREATE TABLE games_played (ID INT,home_team VARCHAR(20),away_team VARCHAR(20),homescore INT,awayscore INT);

/* Create few records in this table */
INSERT INTO games_played VALUES(1,'USA','CAN',0,2);
INSERT INTO games_played VALUES(2,'USA','CAN',1,2);
INSERT INTO games_played VALUES(3,'USA','CAN',3,0);
INSERT INTO games_played VALUES(4,'SWE','CAN',3,2);
INSERT INTO games_played VALUES(5,'CAN','RUS',3,2);
INSERT INTO games_played VALUES(6,'DE','RUS',0,2);


/* Verify */
-- SELECT * FROM games_played;

WITH cte AS (
SELECT home_team,
COUNT(CASE WHEN homescore > awayscore THEN 1 END) AS gamesWon,
COUNT(CASE WHEN awayscore > homescore THEN 1 END) AS gamesLost
FROM games_played
GROUP BY home_team)

SELECT * FROM cte
WHERE gamesWon > gamesLost

Upvotes: 2

iSR5
iSR5

Reputation: 3498

This is a simpler approach, 

SELECT g.home_team AS Team
FROM games_played g
LEFT JOIN games_played Win ON Win.id = g.id AND Win.homescore > Win.awayscore
LEFT JOIN games_played Lost ON Lost.id = g.id AND Lost.homescore < Lost.awayscore
GROUP BY g.home_team
HAVING
COUNT(Win.home_team) > COUNT(Lost.home_team)

Upvotes: -1

Related Questions