sum (distinct x) over - SQL Server Forums

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

General SQL Server Forums

New to SQL Server Programming

sum (distinct x) over

Author

Topic

wypwong
Starting Member

9 Posts

Posted - 2011-11-06 : 07:46:41

Hello

I am learning from O'Reilly SQL Cookbook, and now I am stuck in this particular topic, hope someone here can help me.

I can run sum (x) over (partition by y ) as 'Total' with no problem, as soon as I put distinct in front of x, the query stops working. Error message stated - Incorrect syntax near 'distinct'.

I am using SQL Server 2008 Express.
Thanks in advance.

P

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-11-06 : 10:01:12

distinct is not allowed when aggregation is performed using partition by.

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2011-11-06 : 13:52:37

but you can do the same sum / partition on a derived table which is your distinct set. Something like


SELECT
    sum (x) over (partition by y ) as 'Total' 
FROM
    (
    SELECT DISTINCT x, y
    FROM <table>
    )
    AS distXY

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

wypwong
Starting Member

9 Posts

Posted - 2011-11-06 : 17:52:20

Thanks very much. :)

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-11-07 : 03:58:19

[code]
SELECT SUM(DISTINCT x), y
FROM <table>
GROUP BY y
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources