nested select using count to tally each row in Postgres

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

user330315 · Accepted Answer · 2014-08-29 22:48:01Z

4

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

answered Aug 29, 2014 at 22:48

user330315

Sign up to request clarification or add additional context in comments.

3 Comments

eb moore Over a year ago

One additional complication: if I have another field in the table, called Status ('paid,' 'refunded,' 'placed,' 'deleted), is there a way to have this query exclude certain statuses? For example if I want to run this tally, but for all orders where Status = 'deleted' they should not contribute toward the total count. I'm not sure that's possible since this uses row_number(), but maybe if I add 'WHERE Status <> 'deleted'?

user330315 Over a year ago

@ebmoore: do you want to exclude those rows completely from the result or should those rows have no row number at all or should those rows simply have the row number of the "preceding" row? Please add some sample data and expected output to your question that shows what you want.

eb moore Over a year ago

Apologies for my long delay -- I'd like to just skip those orders in the numbering. For example: 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:

Collectives™ on Stack Overflow

nested select using count to tally each row in Postgres

1 Answer 1

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related