From: | "Osvaldo Kussama" <osvaldo(dot)kussama(at)gmail(dot)com> |
---|---|
To: | Devil™ Dhuvader <gibsosmat(at)gmail(dot)com> |
Cc: | pgsql-sql(at)postgresql(dot)org |
Subject: | Resp.: need help in building a query |
Date: | 2008-11-07 20:51:03 |
Message-ID: | 690707f60811071251w534d7aeej709f3a31233c0479@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-sql |
2008/11/7, Devil™ Dhuvader <gibsosmat(at)gmail(dot)com>:
> its like sum up entries of each user in order table backwards (i.e from last
> entry to the first) and find the entry that has sum > $500.
> If there is some user who didnt even make 500 till now in my shop return the
> first date of transaction/order.
>
> ex:
> Orders(order_id, user_id, amount_paid, create_timestamp)
> values:
> (1, 1, 100, 1)
> (2, 1, 300, 2)
> (3, 2, 100, 2)
> (4, 2, 100, 3)
> (5, 1, 100, 4)
> (6, 1, 200, 5)
> (7, 2, 150, 5)
>
> for user 1: the order_id = 2
> for user 2: the order_id = 3 (coz he couldnt make 500)
>
Try:
bdteste=# SELECT o1.user_id, o1.order_id, '>= 500' AS cond FROM Orders o1
bdteste-# WHERE (SELECT sum(o2.amount_paid) FROM Orders o2 WHERE
o2.user_id = o1.user_id AND o2.order_id > o1.order_id) < 500 AND
bdteste-# (SELECT sum(o2.amount_paid) FROM Orders o2 WHERE
o2.user_id = o1.user_id AND o2.order_id >= o1.order_id) >=500
bdteste-# UNION
bdteste-# SELECT user_id, min(order_id) AS "min order id", '< 500' AS
cond FROM Orders
bdteste-# WHERE user_id IN (SELECT user_id FROM Orders GROUP BY
user_id HAVING sum(amount_paid) < 500)
bdteste-# GROUP BY user_id;
user_id | order_id | cond
---------+----------+--------
1 | 2 | >= 500
2 | 3 | < 500
Osvaldo
From | Date | Subject | |
---|---|---|---|
Next Message | Frank Bax | 2008-11-07 21:00:40 | Re: need help in building a query |
Previous Message | Tom Lane | 2008-11-07 20:45:16 | Re: Res: Finding all tables that have foreign keys referencing a table |