CODEWITHSUNDEEP
sqlpostgresql
Rahul Jain
Rahul Jain

Reputation: 115

Join multiple select queries SQL

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.

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

Upvotes: 0

Views: 166

Answers (2)

Mr. Bhosale
Mr. Bhosale

Reputation: 3096

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

Upvotes: 0

krokodilko
krokodilko

Reputation: 36087

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;

Upvotes: 2

Related Questions