CODEWITHSUNDEEP
sql-serverselectcount
Vlad Danila
Vlad Danila

Reputation: 1372

Get most frequent value from SELECT result in SQL Server

I have 4 columns in my [dbo].[game] table:

By performing the following query, I am able to get as result the id's of the teams that won a game.

select 
    (case 
        when ([dbo].[game].[score_away] < [dbo].[game].[score_home]) 
           then [dbo].[game].[team_home_id] 
           else [dbo].[game].[team_away_id] 
     end) as winning_team_id
from 
    [dbo].[game]

Result example:

12
2
3
5
7
4
...

Is there any way to count and get the id of the team that won most games in the same query?

Upvotes: 0

Views: 39

Answers (2)

amcdermott
amcdermott

Reputation: 1585

Something like

select winning_team_id, count(*)
from ( select (case  when ([dbo].[game].[score_away] < [dbo].[game].[score_home]) 
                          then [dbo].[game].[team_home_id] 
                     else [dbo].[game].[team_away_id] 
               end) as winning_team_id
       from     [dbo].[game] ) winners
group by winning_team_id
order by 2 desc

will give you a list sorted by the team id that won most games.

Use select top 1 to get just the team that won most games,

Upvotes: 1

forpas
forpas

Reputation: 164089

Use that query and group by winning_team_id to get the number of wins.
Then sort descending by the number of wins and get the top row.

select top 1 t.winning_team_id, count(*) counter from (
select 
    (case 
        when ([dbo].[game].[score_away] < [dbo].[game].[score_home]) 
           then [dbo].[game].[team_home_id] 
           else [dbo].[game].[team_away_id] 
     end) as winning_team_id
from 
    [dbo].[game] 
) t
group by t.winning_team_id
order by count(*) desc

Upvotes: 2

Related Questions