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!

---

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