SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 Site Related Forums
 Article Discussion
 Article: SQL Sever 2005: Using OVER() with Aggregate Functions
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

AskSQLTeam
Ask SQLTeam Question

USA
0 Posts

Posted - 05/21/2007 :  09:57:06  Show Profile  Visit AskSQLTeam's Homepage  Reply with Quote
One of new features in SQL 2005 that I haven't seen much talk about is that you can now add aggregate functions to any SELECT (even without a GROUP BY clause) by specifying an OVER() partition for each function. Unfortunately, it isn't especially powerful, and you can't do running totals with it, but it does help you make your code a little shorter and in many cases it might be just what you need.

Article Link.

Scott Pletcher
Starting Member

2 Posts

Posted - 06/20/2007 :  11:33:37  Show Profile  Reply with Quote
Very helpful info, and well presented.
Go to Top of Page

addytude
Starting Member

India
12 Posts

Posted - 02/17/2008 :  13:09:27  Show Profile  Reply with Quote
most informative! Thanks
Go to Top of Page

eldkir
Starting Member

USA
1 Posts

Posted - 03/13/2008 :  12:11:11  Show Profile  Send eldkir an AOL message  Reply with Quote
Great Information!!

I have a question that may be applicable to this function. I am trying to do a count of the number of times customers have purchased from us. For example, I need these 3 buckets; Once, Twice and 3 or more. Would I incorporate the Over function here, or is there something else I should be using? Thanks in advance for your help!
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52325 Posts

Posted - 03/23/2008 :  20:18:24  Show Profile  Reply with Quote
quote:
Originally posted by eldkir

Great Information!!

I have a question that may be applicable to this function. I am trying to do a count of the number of times customers have purchased from us. For example, I need these 3 buckets; Once, Twice and 3 or more. Would I incorporate the Over function here, or is there something else I should be using? Thanks in advance for your help!


You can use OVER in this scenario as follows:-
SELECT tmp.Category,Count(tmp.customer_id)
FROM
(
SELECT t.customer_id,
       CASE
       WHEN t.CustSum =1 THEN 'Once'
       WHEN t.CustSum=2 THEN 'Twice'
       ELSE '3 or more'
       END AS Category
FROM 
(
SELECT DISTINCT c.customer_id,
       SUM(p.purchase_id) OVER (PARTITON BY c.customer_id) AS CustSum
FROM Customer c
INNER JOIN Purchase p
ON p.customer_id=c.customer_id
)t
)tmp
GROUP BY tmp.Category

Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.06 seconds. Powered By: Snitz Forums 2000