pat67 said:
pat67 said:
SELECT a.Player, a.Team, a.Game, a.Win, a.Loss,a.WPct
FROM (SELECT tblStats_All_P.PlayerName as Player,
tblStats_All_P.Team, [Win]+[Loss] AS Game, Sum(tblStats_All_P.Won)
AS Win, Sum(tblStats_All_P.Lost) AS Loss, [Win]/[Game] AS WPct
FROM tblStats_All_P
UNION ALL SELECT tblStats_All_P.PlayerName1 as Player,
tblStats_All_P.Team, [Win]+[Loss] AS Game, Sum(tblStats_All_P.Won)
AS Win, Sum(tblStats_All_P.Lost) AS Loss, [Win]/[Game] AS WPct
FROM tblStats_All_P) as a
GROUP BY a.Player, a.Team;
What I need to do is combine PlayerName and PlayerName1 into Player.
This gives me an error saying the query doesn't include Player as
part of the aggregate function. What am I doing wrong?
Failing to include Player as part of an aggregate function- Hide
quoted text -
- Show quoted text -
Ok. How do I do that?
Well, start by running the individual queries in your union subselect. Each
of those raises an error doesn't it? Start with this one:
SELECT tblStats_All_P.PlayerName as Player, tblStats_All_P.Team,
[Win]+[Loss] AS Game, Sum(tblStats_All_P.Won) AS Win,
Sum(tblStats_All_P.Lost) AS Loss, [Win]/[Game] AS WPct
FROM tblStats_All_P
You have a aggregate functions, the SUM expressions, combined with
non-aggregated fields:
tblStats_All_P.PlayerName as Player,
tblStats_All_P.Team,
[Win]+[Loss] AS Game
[Win]/[Game] AS WPct
You cannot combine aggregates and non-aggregates in a query without using a
GROUP BY clause containing your non-aggregated fields. So at the very least,
you need to change this to (I'm removing the field qualifiers because they
are not needed due to there being only a single table in the FROM clause:
SELECT PlayerName as Player,Team,
, Sum(Won) AS Win,
Sum(Lost) AS Loss
FROM tblStats_All_P
GROUP BY PlayerName,Team
In addition, you have calculations referring to fields that do not yet
exist: [Win],[Loss] and [Game]. This is not allowed. You need to repeat the
expressions, like this:
Sum(Won) + Sum(Lost) As Game. Make a similar modification to that WPct
calculation:
Sum(Won)/(Sum(Won) + Sum(Lost)) AS WPct
To wind up with this:
SELECT PlayerName as Player,Team,
, Sum(Won) AS Win
,Sum(Lost) AS Loss
,Sum(Won) + Sum(Lost) As Game
, Sum(Won)/(Sum(Won) + Sum(Lost)) AS WPct
FROM tblStats_All_P
GROUP BY PlayerName,Team
Try this statement and verify it returns the correct data. The modify the
other one similarly.
SELECT PlayerName1 as Player,.Team
, Sum(tblStats_All_P.Won) AS Win
, Sum(tblStats_All_P.Lost) AS Loss
,Sum(Won) + Sum(Lost) As Game
, Sum(Won)/(Sum(Won) + Sum(Lost)) AS WPct
FROM tblStats_All_P
GROUP BY PlayerName1,Team
Only after each query runs without error and returns the data you need
should you think about unioning them:
SELECT PlayerName as Player,Team,
, Sum(Won) AS Win,
Sum(Lost) AS Loss
,Sum(Won) + Sum(Lost) As Game
, Sum(Won)/(Sum(Won) + Sum(Lost)) AS WPct
FROM tblStats_All_P
GROUP BY PlayerName,Team
UNION ALL
SELECT PlayerName1 as Player,.Team
, Sum(tblStats_All_P.Won) AS Win
, Sum(tblStats_All_P.Lost) AS Loss
,Sum(Won) + Sum(Lost) As Game
, Sum(Won)/(Sum(Won) + Sum(Lost)) AS WPct
FROM tblStats_All_P
GROUP BY PlayerName1,Team
Again, run this and verify it runs correctly. Now substitute it into your
original query:
SELECT a.Player, a.Team, a.Game, a.Win, a.Loss,a.WPct
FROM (
SELECT PlayerName as Player,Team,
, Sum(Won) AS Win
, Sum(Lost) AS Loss
,Sum(Won) + Sum(Lost) As Game
, Sum(Won)/(Sum(Won) + Sum(Lost)) AS WPct
FROM tblStats_All_P
GROUP BY PlayerName,Team
UNION ALL
SELECT PlayerName1 as Player,.Team
, Sum(tblStats_All_P.Won) AS Win
, Sum(tblStats_All_P.Lost) AS Loss
,Sum(Won) + Sum(Lost) As Game
, Sum(Won)/(Sum(Won) + Sum(Lost)) AS WPct
FROM tblStats_All_P
GROUP BY PlayerName1,Team
) as a
GROUP BY a.Player, a.Team;
It still errors, right? That's because you have grouped by fields combined
with fields without aggregates again. I think I will leave the rest as an
exercise for you. You should have enough hints by now.
