Aggregates and subqueries.. I'm stuck

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)

Aggregates and subqueries.. I'm stuck

Author

Topic

Yukite
Starting Member

3 Posts

Posted - 2009-09-28 : 07:08:17

Hello everyone. As the errormessage (Cannot perform an aggregate function on an expression
containing an aggregate or a subquery.) tought me, I can not use subqueries in aggregates. But I just can't figure out how to rewrite the following query so I have no longer a query in the main SELECT statement.

SELECT     CAST(Row_Number() OVER (ORDER BY SUM(COALESCE (Points, 0)) DESC) AS INT) Ranking, SUM(COALESCE (Points *
    (SELECT     PointMultiplicator
      FROM          [StatusType] s
      WHERE      i.CurrentStatusID = s.StatusTypID), 0)) Cnt, 
   u.UserID, Firstname, Lastname, FreezeID
FROM         [User] u LEFT JOIN
                      Idea i ON u.UserID = i.UserID LEFT JOIN
                      Rating r ON r.IdeaID = i.IdeaID
GROUP BY u.UserID, Firstname, Lastname, FreezeID

I hope that someone is able to help me. My SQL skills aren't that good and I'm really stuck on this one.
If you need more information, please tell me so.

Sincerely, Yukite

asgast
Posting Yak Master

149 Posts

Posted - 2009-09-28 : 07:56:29

CREATE TABLE #t(PointMultiplicator int , StatusTypID int)

INSERT #t(PointMultiplicator, StatusTypID)
SELECT PointMultiplicator, StatusTypID
FROM [StatusType] s

SELECT CAST(Row_Number() OVER (ORDER BY SUM(COALESCE (Points, 0)) DESC) AS INT) Ranking, SUM(ISNULL( (Points *t.PointMultiplicator, 0)) Cnt,
u.UserID, Firstname, Lastname, FreezeID
FROM [User] u
LEFT JOIN Idea i ON u.UserID = i.UserID
LEFT JOIN Rating r ON r.IdeaID = i.IdeaID
INNER JOIN #t t on i.CurrentStatusID = t.StatusTypID
GROUP BY u.UserID, Firstname, Lastname, FreezeID

this should work, can't test it right now
the idea is to store your sub query in a table

Yukite
Starting Member

3 Posts

Posted - 2009-09-28 : 08:35:14

Tried this (respectively outsourced the table into a view) and it works like a charm. Thanks alot, agast!
Hopefully I'll get the hang of it soon...

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources