Summary based on search upon two columns

[khawarameer]

Dear All,

I have a table in which i have put up a match wise detail of a local tournament. The fields are Home_Team, Home_Score, Away_Score and Away_Team.

Now i wanted to have a summary of each team with home and away matches counted and goals summed.

I need help, any one?

Regards

Khawar

 

[Darren Bartrup]

I'd change the table to have these fields: Date, Team, Score and Home_Away, Opposing_Team

You can then count and sum scores as:

SELECT Team, SUM(Score)FROM Table1
GROUP BY Team


SELECT Team, Home_Away, SUM(Score)
FROM Table1
GROUP BY Team, Home_Away


SELECT Team, COUNT(Score)
FROM Table1
GROUP BY Team


SELECT Team, Home_Away, COUNT(Score)
FROM Table1
GROUP BY Team, Home_Away

Presuming each team will only play once a day you can link the two sides of the game together using the Date and Opposing_Team fields:

SELECT  T1.Team, 
        T1.Score, 
        T1.Home_Away,
        T2.Team, 
        T2.Score, 
        T2.Home_Away
FROM    Table1 T1 LEFT JOIN Table1 T2 ON
            T1.Opposing_Team = T2.Team AND
            T1.dDate = T2.dDate
WHERE   T1.Home_Away = 'Home'

 

[khawarameer]

Thanks for your kind response Darren,

However if I follow your idea I have to paste each record twice for Score of Home or Away team. Instead in can add match number field in table easily. Will adding match number do any help?


Regards

KhawarAmeer

 

[Darren Bartrup]

If you add a match number - which I'm assuming is unique to each match:

Create a union query called 'MyUnionQuery' which will put the data into the format I suggested on the first post, but with Match_Number rather than Date & Team Name as the main identifier.

SELECT  Home_Team AS Team,        
        Home_Score AS Score,
        'Home' AS Home_Away,
        Match_Number
FROM    Table2
        
UNION ALL SELECT Away_Team,
                 Away_Score,
                 'Away',
                 Match_Number
FROM             Table2

And then as before:

SELECT Team, Home_Away, SUM(Score)
FROM MyUnionQuery
GROUP BY Team, Home_Away

or

SELECT  T1.Team, 
        T1.Score, 
        T1.Home_Away,
        T2.Team, 
        T2.Score, 
        T2.Home_Away
FROM    MyUnionQuery T1 LEFT JOIN MyUnionQuery T2 ON
            T1.Match_Number = T2.Match_Number AND
            T1.Team <> T2.Team
WHERE   T1.Home_Away = 'Home'

 

[khawarameer]

Thanks Darren for your kind responses.
However the queries you suggested are not giving me my desired results. Actually I may have miscommunicated my desired results. I needed this type of output

Suppose I have 5 teams
Team 1
Team 2
Team 3
Team 4
Team 5

And they play with each other I wanted to display summary of each team which should look like this.

Output A

Team Name	Matched Played	Won	Lost	Drawn	G_Scored	G_Against
Team 1	8	8	0	0	20	12

<tbody>
</tbody>

Output B

Team Name	Matched Played	Won	Lost	Drawn	G_Scored	G_Against
Team 2	2	2	0	0	5	3
Team 3	2	2	0	0	5	3
Team 4	2	2	0	0	5	3
Team 5	2	2	0	0	5	3

<tbody>
</tbody>

The output 1 should display Team 1’s complete performance
And Output 1 should display Team 1’s individual performance against each team.

I hope this will create clarity about what actually I am looking for.

Regards


Khawar

 

[strive4peace]

modifying Darren's Union query slightly:

SELECT  Home_Team AS Team,        
        Home_Score AS Score,
        Away_Score AS Against,
        'Home' AS Home_Away,
        IIF(Home_Score > Away_Score, 1, 0) as Won_ ,
        IIF(Home_Score < Away_Score, 1, 0) as Lost_ ,
        IIF(Home_Score = Away_Score, 1, 0) as Drawn_ ,
        Match_Number
FROM    Table2
        
UNION ALL SELECT Away_Team,
            Away_Score AS Score,
            Home_Score AS Against,
                 'Away',
                 IIF(Away_Score < Home_Score, 1, 0) as Won_ ,
                 IIF(Away_Score > Home_Score, 1, 0) as Lost_ ,
                 IIF(Home_Score = Away_Score, 1, 0) as Drawn_,
                 Match_Number
FROM             Table2

then on top of that:

SELECT Team as [Team Name]
, count(Match_Number) as [Matches Played]
, sum(Won_) as Won
, sum(Lost_) as Lost
, sum(Drawn_) as Drawn
, sum(Score) as G_Scored
, sum(Against) as G_Against
FROM MyUnionQuery
GROUP BY Team

 

[khawarameer]

Thank You so much Dear strive4peace for providing your solution. However if you look at my last post you will see that your queries have only provided me solution for Output 1. Can you give the solution for 2nd output as well.

Best regards.

Khawar

 

[khawarameer]

I modified the code a little according to my needs as following

SELECT Away_Team AS Team, Home_Score AS Score, Away_Score AS Against, 'Home' AS Home_Away, IIF(Home_Score > Away_Score, 1, 0) as Won_ , IIF(Home_Score < Away_Score, 1, 0) as Lost_ , IIF(Home_Score = Away_Score, 1, 0) as Drawn_ , Match_NumberFROM Table2WHERE (((Table2.Home_Team)=[What Team?]))


UNION ALL SELECT Home_Team, Away_Score AS Score, Home_Score AS Against, 'Away', IIF(Away_Score > Home_Score, 1, 0) as Won_ , IIF(Away_Score < Home_Score, 1, 0) as Lost_ , IIF(Home_Score = Away_Score, 1, 0) as Drawn_, Match_NumberFROM Table2WHERE (((Table2.Away_Team)=[What Team?]));


</pre>

However with this code i can only manually input a team name which must match with the name available in the table. If there is a way to select the team name from the list of all teams it will work fine.

Any suggestions

Regards

Khawar