nested select using count to tally each row in Pos

Question

I have a table where each row is a unique order with a unique order_id, but users can have multiple rows/orders.

Orders table -> order_id, user_id, orderedat (datetime), sales

I am trying to return a query that calculates, for each order_id, how many previous orders the associated user has made. (Essentially, does this row represent the user's first order? 5th order? 20th order? etc.)

I'm trying something like this nested select, but am getting an error "more than one row returned by a subquery used as an expression."

SELECT
  order_id,
  user_id,
  COUNT (order_id) AS order_n
FROM
  orders
WHERE orderedat >= (
  SELECT
    orderedat
  FROM
    fulfillments
  GROUP BY
    order_id
)
GROUP BY
  order_id

Any thoughts on how to achieve this in postgres?

/////////////// Further complication: with another column called "Status," how to only count rows with specific values in Status? I'd like to just skip orders in the number unless they have a status of "paid" or "placed". For example:

data:
order_id      user_id      orderedat      status
001            max          10/1/14        paid
002            max          10/20/14       placed
003            max          10/21/14       cancelled
004            bill         10/5/14        deleted
005            max          10/31/14       paid
006            bill         10/24/14       placed

results:
order_id      user_id      orderedat      orders_so_far
001            max          10/1/14        1
002            max          10/20/14       2
003            max          10/21/14       null
005            max          10/31/14       3
004            bill         10/5/14        null
006            bill         10/24/14       1

Answer 1

This can be done using a window function:

SELECT order_id,
       user_id,
       orderdat,
       row_number() over (partition by user_id order by orderedat ) as orders_so_far
FROM orders
order by user_id, orderdat