CODEWITHSUNDEEP
sqldatabasepostgresql
HessianMad
HessianMad

Reputation: 567

SQL query percentage with 2 tables

I’m beginning with SQL and I’m having problems when I would like to run some queries. The database has these two tables:

enter image description here

I would like to retrieve the name of the teams with “id_equip” 27 and 58 with the percentage of winning matches with between both.

So far I’ve done: 

SELECT   j.id_eq_local , 
         Avg(res_local) 
FROM     juga AS j 
WHERE    ( 
                  j.id_eq_local = 58 
         OR       j.id_eq_local = 27) 
AND      ( 
                  res_local > res_visitant 
         OR       res_local < res_visitant) 
AND      ( 
                  j.id_eq_visitant = 58 
         OR       j.id_eq_visitant = 27) 
GROUP BY (j.id_eq_local, j.id_eq_visitant);

But it doesn’t give the result which is :

Barcelona - 57

Real Madrid - 28

Which means: Barcelona won the 57% of the matches, Real Madrid the 28% and the remainder finished in tie.

Brief explanation of the database:

Table JUGA:

Table EQUIP:

Some samples of the data: enter image description here

enter image description here

What I want:

enter image description here

Thank you in advance.

Upvotes: 1

Views: 356

Answers (3)

Stephan Lechner
Stephan Lechner

Reputation: 35154

One could use postgre's filter-specifier on aggregate functions, which restricts the rows to be considered by the aggregate function to those fulfilling specific conditions. Thereby, we can compare counts over different sets of rows within the same query:

select 'Real Madrid' as equip,
  100*(count(*) filter (where (id_eq_local > id_eq_visitant and res_local > res_visitant) OR (id_eq_local < id_eq_visitant and res_local < res_visitant))) / count(*) as percentage
from juga
where id_eq_visitant IN (27,58)
  and id_eq_local IN (27,58)

UNION 

select 'Barcelona' as equip,
  100*(count(*) filter (where (id_eq_local < id_eq_visitant and res_local > res_visitant) OR (id_eq_local > id_eq_visitant and res_local < res_visitant))) / count(*) as percentage
from juga
where id_eq_visitant IN (27,58)
  and id_eq_local IN (27,58)

Upvotes: 3

Jeremy Real
Jeremy Real

Reputation: 766

I think this would do it.

SELECT (SUM(WIN_58)/SUM(match_count))*100 AS WIN_PCT_58
      ,(SUM(WIN_27)/SUM(match_count))*100 AS WIN_PCT_27
      ,(SUM(TIE)/SUM(match_count))*100 AS TIE_PCT
FROM (
    SELECT 1 as match_count
          ,CASE WHEN res_local > res_visitant THEN 1 ELSE 0 END AS WIN_58
          ,CASE WHEN res_local = res_visitant THEN 1 ELSE 0 END AS TIE
          ,CASE WHEN res_local < res_visitant THEN 1 ELSE 0 END AS WIN_27
    FROM juga AS j
    WHERE j.id_eq_local = 58 AND j.id_eq_visitant = 27
UNION ALL
    SELECT 1 as match_count
          ,CASE WHEN res_local < res_visitant THEN 1 ELSE 0 END AS WIN_58
          ,CASE WHEN res_local = res_visitant THEN 1 ELSE 0 END AS TIE
          ,CASE WHEN res_local > res_visitant THEN 1 ELSE 0 END AS WIN_27
    FROM juga AS j
    WHERE j.id_eq_visitant = 58 AND j.id_eq_local = 27
)

Upvotes: 2

farghal
farghal

Reputation: 301

I think this is what you are looking for:

select (local.local_wins + guest.guest_wins) / (local.cnt + guest.cnt) as Team1,
       (local.guest_wins + guest.local_wins) / (local.cnt + guest.cnt) as Team2
from 
    (select sum(case when res_local > res_visitant then 1 else 0 end) as local_wins, sum(case when res_visitant > res_local then 1 else 0 end) guest_wins, count(*) as cnt
     from JUGA where id_local = 53 and id_guest = 17) local,

    (select sum(case when res_local > res_visitant then 1 else 0 end) as local_wins, sum(case when res_visitant > res_local then 1 else 0 end) as guest_wins, count(*) as cnt 
     from JUGA where id_local = 17 and id_guest = 53) guest

Upvotes: 0

Related Questions