How to sum a column created by an aggregate func

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 2000 Forums

Transact-SQL (2000)

How to sum a column created by an aggregate func

Author

Topic

Dan
Starting Member

5 Posts

Posted - 2003-02-13 : 15:55:13

I have the following query and i really neeed to get the sum of the result, i'm also trying to avoid cursors:

SELECT sum(count(distinct CONVERT(VARCHAR(10),AnyDateTimeColumn,103)))as something
FROM TABLE
WHERE
NOT RPT_USU_N_CODIGO = 25 AND
RPT_N_STATUS IN (10,13,14,15,16)
AND RPT_PSQ_N_CODIGO = 95
GROUP BY RPT_USU_N_CODIGO

and i get the error:Cannot perform an aggregate function on an expression containing an aggregate or a subquery.

Someone have any idea to round this problem??? thanks a lot!!!

ValterBorges
Master Smack Fu Yak Hacker

1429 Posts

Posted - 2003-02-13 : 17:00:09

SELECT A.RPT_USU_N_CODIGO ,SUM(A.CountDistinct)
FROM
(
SELECT
RPT_USU_N_CODIGO,
COUNT(DISTINCT CONVERT(VARCHAR(10),AnyDateTimeColumn,103)) As CountDistinct
FROM TABLE
WHERE
NOT RPT_USU_N_CODIGO = 25 AND
RPT_N_STATUS IN (10,13,14,15,16)
AND RPT_PSQ_N_CODIGO = 95
GROUP BY RPT_USU_N_CODIGO
) A
GROUP BY A.RPT_USU_N_CODIGO

Dan
Starting Member

5 Posts

Posted - 2003-02-14 : 10:47:41

Hi Valter, thanks a lot for your code. Um abraço do Brasil.

ValterBorges
Master Smack Fu Yak Hacker

1429 Posts

Posted - 2003-02-14 : 11:16:55

Do you know britanico he's a friend.
He's member of sql team and he's brazilian too.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources