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.