Union of two queries to get totals

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Union of two queries to get totals

Author

Topic

kamii47
Constraint Violating Yak Guru

353 Posts

Posted - 2008-05-19 : 08:08:25

I following two queries
First query is
SELECT HomeTeam, 1 AS Pld, CASE WHEN HomeScore > AwayScore THEN 1 ELSE 0 END AS Won, CASE WHEN HomeScore = AwayScore THEN 1 ELSE 0 END AS Draw, CASE WHEN HomeScore < AwayScore THEN 1 ELSE 0 END AS Lost, HomeScore AS Scored, AwayScore AS Against, HomeScore - AwayScore AS Agg, CASE WHEN HomeScore > AwayScore THEN 3 ELSE 0 END AS Pts FROM tblFixtures WHERE (CompID = 1) AND (HomeScore IS NOT NULL)

and second query is

SELECT AwayTeam, 1 AS Pld, CASE WHEN HomeScore < AwayScore THEN 1 ELSE 0 END AS Won, CASE WHEN HomeScore = AwayScore THEN 1 ELSE 0 END AS Draw, CASE WHEN HomeScore > AwayScore THEN 1 ELSE 0 END AS Lost, AwayScore AS Scored, HomeScore AS Against, AwayScore - HomeScore AS Agg, CASE WHEN HomeScore < AwayScore THEN 3 WHEN HomeScore = AwayScore THEN 1 ELSE 0 END AS Pts FROM tblFixtures WHERE (CompID = 1) AND (HomeScore IS NOT NULL)
what i need is
I want to create another joint result of two queries which adds up all the totals from each of the other two queries.

Result some thing like

Team, Pld, W, D, L, F, A, Agg, Pts

Kamran Shahid
Sr. Software Engineer(MCSD.Net)
www.netprosys.com

RickD
Slow But Sure Yak Herding Master

3608 Posts

Posted - 2008-05-19 : 08:27:36

The queries are the same, so why would you want to get the same results twice?

kamii47
Constraint Violating Yak Guru

353 Posts

Posted - 2008-05-19 : 08:44:13

They are different [Post edited previously pasted the same query twice]

Kamran Shahid
Sr. Software Engineer(MCSD.Net)
www.netprosys.com

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-05-19 : 09:12:01

First query is missing a WHEN for tie games.

SELECT		HomeTeam,
		1 AS Pld,
		CASE WHEN HomeScore > AwayScore THEN 1 ELSE 0 END AS Won,
		CASE WHEN HomeScore = AwayScore THEN 1 ELSE 0 END AS Draw,
		CASE WHEN HomeScore < AwayScore THEN 1 ELSE 0 END AS Lost,
		HomeScore AS Scored,
		AwayScore AS Against,
		HomeScore - AwayScore AS Agg,
		CASE WHEN HomeScore > AwayScore THEN 3 ELSE 0 END AS Pts
FROM		tblFixtures
WHERE		CompID = 1
		AND HomeScore IS NOT NULL

SELECT		AwayTeam,
		1 AS Pld,
		CASE WHEN HomeScore < AwayScore THEN 1 ELSE 0 END AS Won,
		CASE WHEN HomeScore = AwayScore THEN 1 ELSE 0 END AS Draw,
		CASE WHEN HomeScore > AwayScore THEN 1 ELSE 0 END AS Lost,
		AwayScore AS Scored,
		HomeScore AS Against,
		AwayScore - HomeScore AS Agg,
		CASE WHEN HomeScore < AwayScore THEN 3 WHEN HomeScore = AwayScore THEN 1 ELSE 0 END AS Pts
FROM		tblFixtures
WHERE		CompID = 1
		AND HomeScore IS NOT NULL

E 12°55'05.25"
N 56°04'39.16"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-05-19 : 09:15:26

[code]SELECT HomeTeam,
1 AS Pld,
CASE
WHEN HomeScore > AwayScore THEN 1
ELSE 0
END AS Won,
CASE
WHEN HomeScore = AwayScore THEN 1
ELSE 0
END AS Draw,
CASE
WHEN HomeScore < AwayScore THEN 1
ELSE 0
END AS Lost,
HomeScore AS Scored,
AwayScore AS Against,
HomeScore - AwayScore AS Agg,
CASE
WHEN HomeScore > AwayScore THEN 3
WHEN HomeScore = AwayScore THEN 1
ELSE 0
END AS Pts
FROM tblFixtures
WHERE CompID = 1
AND HomeScore IS NOT NULL

UNION ALL

SELECT AwayTeam,
1 AS Pld,
CASE
WHEN HomeScore < AwayScore THEN 1
ELSE 0
END AS Won,
CASE
WHEN HomeScore = AwayScore THEN 1
ELSE 0
END AS Draw,
CASE
WHEN HomeScore > AwayScore THEN 1
ELSE 0
END AS Lost,
AwayScore AS Scored,
HomeScore AS Against,
AwayScore - HomeScore AS Agg,
CASE
WHEN HomeScore < AwayScore THEN 3
WHEN HomeScore = AwayScore THEN 1
ELSE 0
END AS Pts
FROM tblFixtures
WHERE CompID = 1
AND HomeScore IS NOT NULL[/code]

E 12°55'05.25"
N 56°04'39.16"

kamii47
Constraint Violating Yak Guru

353 Posts

Posted - 2008-05-19 : 10:01:04

brilliant, saves my day
Is it possible to group by "Team" in the following SQL Datasource?

Each row has a numbers in their respective columns and need to be added up and only one row per team is shown showing the total?

SELECT HomeTeam AS Team, 1 AS Pld, CASE WHEN HomeScore > AwayScore THEN 1 ELSE 0 END AS Won,
CASE WHEN HomeScore = AwayScore THEN 1 ELSE 0 END AS Draw, CASE WHEN HomeScore < AwayScore THEN 1 ELSE 0 END AS Lost,
HomeScore AS Scored, AwayScore AS Against, HomeScore - AwayScore AS Agg,
CASE WHEN HomeScore > AwayScore THEN 3 WHEN HomeScore = AwayScore THEN 1 ELSE 0 END AS Pts
FROM tblFixtures
WHERE (CompID = 1) AND (HomeScore IS NOT NULL)
UNION ALL
SELECT AwayTeam AS Team, 1 AS Pld, CASE WHEN HomeScore < AwayScore THEN 1 ELSE 0 END AS Won,
CASE WHEN HomeScore = AwayScore THEN 1 ELSE 0 END AS Draw, CASE WHEN HomeScore > AwayScore THEN 1 ELSE 0 END AS Lost,
AwayScore AS Scored, HomeScore AS Against, AwayScore - HomeScore AS Agg,
CASE WHEN HomeScore < AwayScore THEN 3 WHEN HomeScore = AwayScore THEN 1 ELSE 0 END AS Pts
FROM tblFixtures AS tblFixtures_1
WHERE (CompID = 1) AND (HomeScore IS NOT NULL)

Kamran Shahid
Sr. Software Engineer(MCSD.Net)
www.netprosys.com

RickD
Slow But Sure Yak Herding Master

3608 Posts

Posted - 2008-05-19 : 10:02:02

Adding to Peters query:


select
	HomeTeam,
	sum(won) as Won,
	sum(Draw) as Draw,
	Sum(Lost) as Lost,
	Sum(Scored) as Scored,
	Sum(Against) as Against,
	Sum(Agg) as Agg,
	Sum(Pts) as Pts
from
(SELECT		HomeTeam,
		1 AS Pld,
		CASE
			WHEN HomeScore > AwayScore THEN 1
			ELSE 0
		END AS Won,
		CASE
			WHEN HomeScore = AwayScore THEN 1
			ELSE 0
		END AS Draw,
		CASE
			WHEN HomeScore < AwayScore THEN 1
			ELSE 0
		END AS Lost,
		HomeScore AS Scored,
		AwayScore AS Against,
		HomeScore - AwayScore AS Agg,
		CASE
			WHEN HomeScore > AwayScore THEN 3
			WHEN HomeScore = AwayScore THEN 1
			ELSE 0
		END AS Pts
FROM		tblFixtures
WHERE		CompID = 1
		AND HomeScore IS NOT NULL

UNION ALL

SELECT		AwayTeam,
		1 AS Pld,
		CASE
			WHEN HomeScore < AwayScore THEN 1
			ELSE 0
		END AS Won,
		CASE
			WHEN HomeScore = AwayScore THEN 1
			ELSE 0
		END AS Draw,
		CASE
			WHEN HomeScore > AwayScore THEN 1
			ELSE 0
		END AS Lost,
		AwayScore AS Scored,
		HomeScore AS Against,
		AwayScore - HomeScore AS Agg,
		CASE
			WHEN HomeScore < AwayScore THEN 3
			WHEN HomeScore = AwayScore THEN 1
			ELSE 0
		END AS Pts
FROM		tblFixtures
WHERE		CompID = 1
		AND HomeScore IS NOT NULL) a
group by HomeTeam

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-05-19 : 10:12:32

[code]DECLARE @Sample TABLE
(
HomeTeam VARCHAR(10),
HomeScore INT,
AwayTeam VARCHAR(10),
AwayScore INT
)

INSERT @Sample
SELECT 'SQLTeam', 10, 'Peso', 10 UNION ALL
SELECT 'SQLTeam', 20, 'Kamii47', 0 UNION ALL
SELECT 'Peso', 15, 'Kamii47', 1 UNION ALL
SELECT 'Kamii47', 1, 'Peso', 2

SELECT p.TeamName,
COUNT(*) AS Pld,
SUM( CASE
WHEN p.Game = 'HomeTeam' AND p.HomeScore > p.AwayScore THEN 1
WHEN p.Game = 'AwayTeam' AND p.HomeScore < p.AwayScore THEN 1
ELSE 0
END) AS Won,
SUM( CASE
WHEN p.HomeScore = p.AwayScore THEN 1
ELSE 0
END) AS Draw,
SUM( CASE
WHEN p.Game = 'HomeTeam' AND p.HomeScore < p.AwayScore THEN 1
WHEN p.Game = 'AwayTeam' AND p.HomeScore > p.AwayScore THEN 1
ELSE 0
END) AS Won,
SUM(p.AwayScore) AS Scored,
SUM(p.HomeScore) AS Against,
SUM(p.AwayScore - p.HomeScore) AS Agg,
SUM( CASE
WHEN p.Game = 'HomeTeam' AND p.HomeScore > p.AwayScore THEN 3
WHEN p.Game = 'AwayTeam' AND p.HomeScore < p.AwayScore THEN 3
WHEN p.HomeScore = p.AwayScore THEN 1
ELSE 0
END) AS Pts
FROM @Sample AS s
UNPIVOT (
TeamName
FOR Game IN (s.HomeTeam, s.AwayTeam)
) AS p
GROUP BY p.TeamName
ORDER BY 9 DESC,
3 DESC[/code]

E 12°55'05.25"
N 56°04'39.16"

kamii47
Constraint Violating Yak Guru

353 Posts

Posted - 2008-09-05 : 16:02:50

Hi Guys,
My above problem is solved.
You guysy helped me with a query to create a football league table.

I just wandered if you could help me finish this off?

I have the following tables and SQL statment which works out a league table for me from the given results.

I am now trying to add a bit more to this:

If the Walkover column = 1 then that row will be a home walkover meaning that the score will be 0(Home Score) - 0(Away Score) but the home team will recieve 3 points, and away team recieves 0 points, win column will be 1 and the played colum will be 1 as well.

If the Walkover column = 2 then that row will be a home walkover meaning that the score will be 0(Home Score) - 0(Away Score) but the away team will recieve 3 points, and home team recieves 0 points, win column will be 1 and the played colum will be 1 as well.

If the Walkover column = 0 then that row the row will be calculated as normal.

tblFixture
FixtureID
CompID (Division ID Name)
HomeTeam (ID from tblTeam)
AwayTeam (ID from tblTeam)
HomeScore
AwayScore
Walkover (2, 1 or 0)

tblTeam
TeamID
TeamName

SQL Statement:

SELECT a.HomeTeam, SUM(a.Pld) AS Pld, SUM(a.Won) AS Won, SUM(a.Draw) AS Draw, SUM(a.Lost) AS Lost, SUM(a.Scored) AS Scored, SUM(a.Against)
AS Against, SUM(a.Agg) AS Agg, SUM(a.Pts) AS Pts, tblTeam.TeamName
FROM (SELECT HomeTeam, 1 AS Pld, CASE WHEN HomeScore > AwayScore THEN 1 ELSE 0 END AS Won,
CASE WHEN HomeScore = AwayScore THEN 1 ELSE 0 END AS Draw, CASE WHEN HomeScore < AwayScore THEN 1 ELSE 0 END AS Lost,
HomeScore AS Scored, AwayScore AS Against, HomeScore - AwayScore AS Agg,
CASE WHEN HomeScore > AwayScore THEN 3 WHEN HomeScore = AwayScore THEN 1 ELSE 0 END AS Pts
FROM tblFixtures
WHERE (CompID = @CompID) AND (HomeScore IS NOT NULL)
UNION ALL
SELECT AwayTeam, 1 AS Pld, CASE WHEN HomeScore < AwayScore THEN 1 ELSE 0 END AS Won,
CASE WHEN HomeScore = AwayScore THEN 1 ELSE 0 END AS Draw, CASE WHEN HomeScore > AwayScore THEN 1 ELSE 0 END AS Lost,
AwayScore AS Scored, HomeScore AS Against, AwayScore - HomeScore AS Agg,
CASE WHEN HomeScore < AwayScore THEN 3 WHEN HomeScore = AwayScore THEN 1 ELSE 0 END AS Pts
FROM tblFixtures AS tblFixtures_1
WHERE (CompID = @CompID) AND (HomeScore IS NOT NULL)) AS a INNER JOIN
tblTeam ON a.HomeTeam = tblTeam.TeamID
GROUP BY a.HomeTeam, tblTeam.TeamName
ORDER BY Pts DESC, Agg DESC, Scored DESC

Kamran Shahid
Sr. Software Engineer(MCSD.Net,MCPD.net)
www.netprosys.com

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources