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.
| Author |
Topic |
|
tech_1
Posting Yak Master
129 Posts |
 Posted - 2010-09-28 : 12:17:49
|
hmm, I have some tables which are joined. I am trying to do a query but bringing back some fields from different joined tables.by my calculations, and test, it should return back 51 records overall but in the query with the joins/group by it returns 13!yes, there are valid relational records joined.quote:
WITH CompanyScores AS ( SELECT ROW_NUMBER() OVER (ORDER BY TotalPoints DESC) AS Row, c.CompanyID, c.CompanyName, pta.TargetPoints, SUM(ua.TotalPoints) AS [TotalPoints] FROM Company c INNER JOIN Users u ON u.CompanyID = c.CompanyID INNER JOIN UserAttributes ua ON ua.UserID = u.UserID INNER JOIN PartnerType pt ON pt.PartnerTypeID = c.PartnerTypeID INNER JOIN PartnerTypeAttributes pta ON pta.PartnerTypeID = pt.PartnerTypeID WHERE c.PartnerTypeID = @partnerTypeID GROUP BY c.CompanyName, c.CompanyID, ua.TotalPoints, pta.TargetPoints) SELECT * FROM CompanyScores
doing this, returns the right results I believe:quote:
SELECT c.CompanyName FROM Company c WHERE c.PartnerTypeID = 1 --param
any ideas? I think the group by is the problem here.im trying to calculate the sum for each company on a field (TotalPoints)Totalpoints for the company is combining the users associated for that company and the totalpoints is in the userattributes table which is a joining table to the users table (a user has attributes)so, for each company I want to get the totalpoints for that company (of a specific partner type) |
|
|
X002548
Not Just a Number
15586 Posts |
|
|
tech_1
Posting Yak Master
129 Posts |
Posted - 2010-09-28 : 13:08:36
|
on what?This seems to work but it doesnt recognise "TotalPoints" :-(quote:
WITH CompanyScores AS ( SELECT c.CompanyID, c.CompanyName, ISNULL((SELECT SUM(ua.TotalPoints) AS [TotalPoints] FROM UserAttributes ua INNER JOIN Users u ON u.UserID = ua.UserID WHERE u.CompanyID = c.CompanyID), 0) AS [TotalPoints], ROW_NUMBER() OVER (ORDER BY TotalPoints DESC) AS Row FROM Company c INNER JOIN PartnerType pt ON pt.PartnerTypeID = c.PartnerTypeID INNER JOIN PartnerTypeAttributes pta ON pta.PartnerTypeID = pt.PartnerTypeID WHERE c.PartnerTypeID = @partnerTypeID) SELECT * FROM CompanyScores WHERE Row BETWEEN (@pageNumber - 1) * @pageSize + 1 AND @pageNumber * @pageSize ORDER BY TotalPoints DESC
|
 |
|
|
|
|
|
|
|
Query returns unexpected results