Select Query MySql

Question

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

Community · Accepted Answer · 2017-05-23 11:53:01Z

1

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)

edited May 23, 2017 at 11:53

CommunityBot

11 silver badge

answered Dec 2, 2016 at 9:32

Flexicoder

8,5615 gold badges45 silver badges58 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Rahul · Accepted Answer · 2016-12-02 09:32:29Z

1

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

answered Dec 2, 2016 at 9:32

Rahul

78.1k14 gold badges80 silver badges132 bronze badges

1 Comment

KhawarAmeerMalik Over a year ago

My dear Rahul, Thank for your response. However i am getting same result with your suggested command.

Collectives™ on Stack Overflow

Select Query MySql

2 Answers 2

Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related