CODEWITHSUNDEEP
mysql
KhawarAmeerMalik
KhawarAmeerMalik

Reputation: 93

Select Query MySql

I have a table with six columns as following
Home_Team | Home_Score | Away_Score | Away_Team | Home_Result | Away_Result

I want to select the output of Home_Team record of win, loos & tie matches.

I used the following query however instead of counting individual totals it is showing count if all matches in each category.

select a.Home_Team,  
if(a.Home_Result = 'Win', count(a.Home_Result), 0) as Win,  
if(b.Home_Result = 'Loss', count(b.Home_Result), 0) as Loss 
from nsfa.result_main_bck as a  
join nsfa.result_main_bck as b   
on a.Home_Team = b.Home_Team  
where a.Home_Result = 'Win' and b.Home_Result = 'Loss'
Group by 1

What could be wrong in this code

I am using MySql by the way.

Regards

Upvotes: 0

Views: 55

Answers (2)

Flexicoder
Flexicoder

Reputation: 8501

Can you not use something like this https://stackoverflow.com/a/13075582/285190 not sure why you are joining the table on its self

So something along the lines of 

select a.Home_Team,
SUM(case when a.Home_Result = 'Win' then 1 else 0 end) as Win
SUM(case when a.Home_Result = 'Loss' then 1 else 0 end) as Loss
from nsfa.result_main_bck as a
where a.Home_Result = 'Win' or a.Home_Result = 'Loss'
Group By a.Home_Team

(NOT TESTED)

Upvotes: 1

Rahul
Rahul

Reputation: 77866

if(a.Home_Result = 'Win', count(a.Home_Result), 0) as Win,

It's a wrong approach rather yo should use CASE expression along with aggregate function like SUM()

sum(case when a.Home_Result = 'Win' then 1 else 0 end) as Win

Upvotes: 1

Related Questions