Union Query Help 1

[mattm31]

A friend has asked me if i can automate his
Cricket league table, I've had a go but am not
familiar with running Union querys.

I've got this far upto now.

SELECT Team_list.Team AS Team, Count(LCL.home_team)AS [HP],
Sum(Case LCL.home_points When '5' Then 1 Else 0 END)AS [HW 5],
Sum(Case LCL.home_points When '4' Then 1 Else 0 END)AS [HW 4],
Sum(Case LCL.home_points When '3' Then 1 Else 0 END)AS [HT 3],
Sum(Case LCL.home_points When '2' Then 1 Else 0 END)AS [HA 2],
Sum(Case LCL.home_points When '1' Then 1 Else 0 END)AS [HD 1],
Sum(Case LCL.home_points When '0' Then 1 Else 0 END)AS [HL 0],
Sum(LCL.home_points)AS [Points]

FROM LCL INNER JOIN Team_list ON LCL.home_Team = Team_List.team

GROUP BY Team_List.Team
Order By [Points]DESC, [HW 5]DESC

SELECT Team_list.Team AS Team, Count(LCL.away_team)AS [AP],
Sum(Case LCL.away_points When '5' Then 1 Else 0 END)AS [AW 5],
Sum(Case LCL.away_points When '4' Then 1 Else 0 END)AS [AW 4],
Sum(Case LCL.away_points When '3' Then 1 Else 0 END)AS [AT 3],
Sum(Case LCL.away_points When '2' Then 1 Else 0 END)AS [AA 2],
Sum(Case LCL.away_points When '1' Then 1 Else 0 END)AS [AD 1],
Sum(Case LCL.away_points When '0' Then 1 Else 0 END)AS [AL 0],
Sum(LCL.away_points)AS [Points]

FROM LCL INNER JOIN Team_list ON LCL.Away_Team = Team_List.team

GROUP BY Team_List.Team
Order By [Points]DESC, [AW 5]DESC

Which gives me the right result.
but i need to know how to display
them as combined.

ie. Sum [HP]+[AP] AS Played

Any help please ?

 

[jbenson001]

Can you show what results you get and how you want them to look (some sample data)?

 

[mattm31]

Sorry yes.

The first query gives the results from the Home games.

Team	HP	HW5	Points
Stand	13	6	36
Irlam	13	4	33

The second deals with the Away results.

Team	HP	HW5	Points
Irlam	13	4	24
Stand	13	2	17

And what i want to display are the totals
so it would be.

Team	TP	HW5	Points
Irlam	26	8	57
Stand	26	8	53

 

[monksnake]

Simply do the following:

1. Take out the order by clause at the end of the first query.

2. Add Union after the group by clause.

...
FROM LCL INNER JOIN Team_list ON LCL.home_Team = Team_List.team

GROUP BY Team_List.Team
[!]Order By [Points]DESC, [HW 5]DESC[/!] //Remove This
[!] UNION [/!]
SELECT Team_list.Team AS Team, Count(LCL.away_team)AS [AP], ...

[small]"I see pretty girls everywhere I look, everywhere I look, everywhere I look. - Band song on movie "The Ringer"[/small]
<.

 

[mattm31]

That now lists each team twice and
doesn't add the columns.

 

[AlexCuse]

Try this. It uses the union query as a derived table, and then draws the proper sums from that.

Select a.Team, sum(a.HP) as TP, sum(a.HW5) as HW5, sum(a.POINTS) as POINTS
from
(
SELECT Team_list.Team AS Team, Count(LCL.home_team)AS [HP],
Sum(Case LCL.home_points When '5' Then 1 Else 0 END)AS [HW 5],
Sum(Case LCL.home_points When '4' Then 1 Else 0 END)AS [HW 4],
Sum(Case LCL.home_points When '3' Then 1 Else 0 END)AS [HT 3],
Sum(Case LCL.home_points When '2' Then 1 Else 0 END)AS [HA 2],
Sum(Case LCL.home_points When '1' Then 1 Else 0 END)AS [HD 1],
Sum(Case LCL.home_points When '0' Then 1 Else 0 END)AS [HL 0],
Sum(LCL.home_points)AS [Points]
FROM LCL INNER JOIN Team_list ON LCL.home_Team = Team_List.team
GROUP BY Team_List.Team

UNION
SELECT Team_list.Team AS Team, Count(LCL.away_team)AS [AP],
Sum(Case LCL.away_points When '5' Then 1 Else 0 END)AS [AW 5],
Sum(Case LCL.away_points When '4' Then 1 Else 0 END)AS [AW 4],
Sum(Case LCL.away_points When '3' Then 1 Else 0 END)AS [AT 3],
Sum(Case LCL.away_points When '2' Then 1 Else 0 END)AS [AA 2],
Sum(Case LCL.away_points When '1' Then 1 Else 0 END)AS [AD 1],
Sum(Case LCL.away_points When '0' Then 1 Else 0 END)AS [AL 0],
Sum(LCL.away_points)AS [Points]
FROM LCL INNER JOIN Team_list ON LCL.Away_Team = Team_List.team
GROUP BY Team_List.Team
) a
GROUP BY a.Team
Order By a.Points DESC

(not tested)

Hope it helps,

Alex

Ignorance of certain subjects is a great part of wisdom

 

[mattm31]

Thanks Alex

I get this error

Server: Msg 207, Level 16, State 3, Line 1
Invalid column name 'HW5'.

 

[mattm31]

Sorry Alex it was fine.

Thanks for the help.

 

[AlexCuse]

I did not see that you had a space in there (you should really try to avoid having spaces in your column names)

Try this:

Select a.Team, sum(a.HP) as TP, sum(a.[HW 5]) as HW5, sum(a.POINTS) as POINTS
from
(
SELECT Team_list.Team AS Team, Count(LCL.home_team)AS [HP],
Sum(Case LCL.home_points When '5' Then 1 Else 0 END)AS [HW 5],
Sum(Case LCL.home_points When '4' Then 1 Else 0 END)AS [HW 4],
Sum(Case LCL.home_points When '3' Then 1 Else 0 END)AS [HT 3],
Sum(Case LCL.home_points When '2' Then 1 Else 0 END)AS [HA 2],
Sum(Case LCL.home_points When '1' Then 1 Else 0 END)AS [HD 1],
Sum(Case LCL.home_points When '0' Then 1 Else 0 END)AS [HL 0],
Sum(LCL.home_points)AS [Points]
FROM LCL INNER JOIN Team_list ON LCL.home_Team = Team_List.team
GROUP BY Team_List.Team

UNION
SELECT Team_list.Team, Count(LCL.away_team),
Sum(Case LCL.away_points When '5' Then 1 Else 0 END),
Sum(Case LCL.away_points When '4' Then 1 Else 0 END),
Sum(Case LCL.away_points When '3' Then 1 Else 0 END),
Sum(Case LCL.away_points When '2' Then 1 Else 0 END),
Sum(Case LCL.away_points When '1' Then 1 Else 0 END),
Sum(Case LCL.away_points When '0' Then 1 Else 0 END),
Sum(LCL.away_points)
FROM LCL INNER JOIN Team_list ON LCL.Away_Team = Team_List.team
GROUP BY Team_List.Team
) a
GROUP BY a.Team
Order By a.Points DESC

Hope this will help,

Alex

Ignorance of certain subjects is a great part of wisdom