GROUP BY i SQL2005
Hej Jag skulle vilja grupperaa listan på DivisionSeasonTeamTeamNbr.
<Code c#>
SELECT b.TeamName Lag, COUNT(*) AS S,
COUNT(CASE WHEN points = 2 THEN points END) AS V,
COUNT(CASE WHEN points = 1 THEN points END) AS O,
COUNT(CASE WHEN points = 0 THEN points END) AS F,
str(sum(a.MatchHomeTeamResult),3,0)+'-'+convert(varchar(3),sum(a.MatchAwayTeamResult)) TOTAL,
SUM(a.points) AS P
FROM (SELECT MatchDivisionId, MatchSeason, ds.DivisionSeasonTeamTeamNbr, MatchHomeTeamId, MatchHomeTeamResult, MatchAwayTeamResult,
SIGN(MatchHomeTeamResult - MatchAwayTeamResult) + 1 AS points
FROM bo_Match
UNION ALL
SELECT MatchDivisionId, MatchSeason, ds.DivisionSeasonTeamTeamNbr, MatchAwayTeamId, MatchAwayTeamResult, MatchHomeTeamResult,
SIGN(MatchAwayTeamResult - MatchHomeTeamResult) + 1 AS points
FROM bo_Match AS Match_1 ) AS a
INNER JOIN bo_Team AS b ON (a.MatchHomeTeamId = b.TeamId)
INNER JOIN bo_DivisionSeasonTeam ds ON (a.MatchHomeTeamId = ds.DivisionSeasonTeamTeamId AND ds.DivisionSeasonTeamSeasonId = 2009)
WHERE (a.MatchDivisionId = '700222' AND MatchSeason = 2009 AND a.MatchHomeTeamResult > 0 AND a.MatchAwayTeamResult > 0 )
GROUP BY ds.DivisionSeasonTeamTeamNbr, b.TeamName
ORDER BY P DESC, TOTAL DESC</Code>
Om jag lägger till DivisionSeasonTeamTeamNbr i GROUP BY får jag error
Msg 207, Level 16, State 1, Line 17
Msg 4104, Level 16, State 1, Line 1
The multi-part identifier "ds.DivisionSeasonTeamTeamNbr" could not be bound.
Msg 4104, Level 16, State 1, Line 1
The multi-part identifier "ds.DivisionSeasonTeamTeamNbr" could not be bound.
Msg 207, Level 16, State 1, Line 17
Invalid column name 'DivisionSeasonTeamTeamNbr'.
Svara
Sv: GROUP BY i SQL2005
Ska du referera till DivisionSeasonTeamTeamNbr i GROUP BY skriver du: a.DivisionSeasonTeamTeamNbr.
Inte ds.DivisionSeasonTeamTeamNbr.
Detta eftersom frågan som hämtar ut ds.DivisionSeasonTeamTeamNbr är "aliasierad" som a.
Svara