Join multiple select queries SQL

Question

I have two queries with different where clauses,and I need to join both the query to get a single result table.

First query:

SELECT 
    players.id,player_name,count(matches.winner) as wintotal 
FROM 
    matches, players 
WHERE 
    matches.winner = players.id 
GROUP BY 
    players.id;

It returns these results:

 id | player_name |      wintotal 
 45 | Vijay       |        2
 43 | Rahul       |        1
 46 | Shinoy      |        1
 48 | Sunil       |        2
 44 | Adarsh      |        4

Second query :

SELECT 
    players.id, player_name, count(*) as totalgames 
FROM 
    matches, players 
WHERE 
    matches.winner = players.id or matches.loser = players.id 
GROUP BY 
    players.id;

Returns:

 id | player_name |         Total Matches
 45 | Vijay       |          4
 43 | Rahul       |          2
 46 | Shinoy      |          4
 48 | Sunil       |          2
 44 | Adarsh      |          6
 47 | Pranjal     |          2

In these two queries, the where clause is different for both queries and the last column is different.

First query returns total wins by players
Second query returns total matches played by player

How can I join both queries to get both columns wins and total matches in single query?

Expected output:

 id | player_name |         Total Matches | wintotal
 45 | Vijay       |          4            |          2
 43 | Rahul       |          2            |          1
 46 | Shinoy      |          4            |          1
 48 | Sunil       |          2            |          2
 44 | Adarsh      |          6            |          4
 47 | Pranjal     |          2            |          0

Thanks

Bad habits to kick : using old-style JOINs - that old-style comma-separated list of tables style was replaced with the proper ANSI JOIN syntax in the ANSI-92 SQL Standard (more than 20 years ago) and its use is discouraged — marc_s
– marc_s, Commented Dec 21, 2016 at 7:23

krokodilko · Accepted Answer · 2016-12-21 06:00:36Z

2

try:

select players.id,
       player_name,
       count(case when matches.winner=players.id then 1 end) as wintotal ,
       count(*) as totalgames
from matches
join players 
on matches.winner=players.id or matches.loser=players.id
group by players.id, 
         player_name;

edited Dec 21, 2016 at 6:00

answered Dec 21, 2016 at 5:37

krokodilko

36.3k7 gold badges62 silver badges86 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Mr. Bhosale · Accepted Answer · 2016-12-21 06:01:30Z

0

Check This.

            select id , player_name ,Total_Matches , wintotal

            (
            select players.id,player_name,count(matches.winner) as wintotal from matches,players where matches.winner=players.id 
            group by players.id
            ) A,

            (
            select players.id,player_name,count(*) as Total_Matches from matches,players where matches.winner=players.id or 
            matches.loser=players.id 
            group by players.id
            ) B

            where A.id=B.ID

answered Dec 21, 2016 at 6:01

Mr. Bhosale

3,1161 gold badge19 silver badges37 bronze badges

Collectives™ on Stack Overflow

Join multiple select queries SQL

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related