SQLTeam.com | Weblogs | Forums

Filtering by count


#1

I am trying to write a query that will display a date, ID#, name and amount from an order table. I need to select all rows for a customer who made five or more purchases. I am not sure how I can filter the report and show only those customers that have more than 5 order and display all five orders. The following will display all rows and add the count of orders with that ID but when I filter it I cannot use the window function in my where clause.

select order_date, id, Name, amount, count(id) over (partition by id) as count
from orders

#2

Never mind, I think I got it.

with CTE as (
select order_date, id, Name, amount, count(id) over (partition by id) as count
from orders
)
select * from CTE
where count > 5