CODEWITHSUNDEEP
sqloracle-databasecount
southernKid33
southernKid33

Reputation: 351

How to use Max() combining two subqueries

I have a table with information about basketball matches and I need to get the team which has won more matches. The table is like the following one:

EQ1     EQ2     DATE       RES1   RES2
--------------------------------------
CAL     VAL     21/10/16    72     82
UNI     VAL     24/02/17    58     68
RMA     FCB     02/11/16    81     64

Where e1 is the local team and res1 its points scored, e2 the visitor team and res2 its points scored.

I have tried different things with no results, and there is not answer in stackoverflows that seems to solve it. I know that I can know how many matches a team won with the following query:

select * 
from encuentros 
where (eq1 = e1.eq1 and res1 > res2) 
   or (eq2 = e1.eq2 and res2 > res1);

This is, being local and winning or being the visitor and winning. Now I need to do that for every team, and get the maximum amount of matches won

Upvotes: 0

Views: 35

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269503

Use conditional logic to get the winning team and then aggregate:

select winning_team, count(*)
from (select (case when res1 > res2 then eq1 else eq2 end) as winning_team
      from t
     ) t
group by winning_team
order by count(*) desc;

To fetch only one row, either use fetch first 1 row only in Oracle 12c+. Or use a subquery and rownum or analytic functions.

Upvotes: 2

Related Questions