Postgres: GROUP BY several column

Question

I have two table in this example.

( example column name )

First is the product

product_id | product_text

Second table is Price.

price_productid | price_datestart | price_price

Let's just say I have multiple datestart with the same product. How can I get the actual price ?

If I use GROUP BY in Postgres, with all the selected column, 2 row may come for the same product. Because the column price_datestart is different.

Example :

product_id : 1
product_text : "Apple Iphone"


price_productid : 1
price_datestart :"2013-10-01"
price_price :"99"


price_productid : 1
price_datestart :"2013-12-01"
price_price :"75"

If I try this :

SELECT price_productid,price_datestart,price_price,product_text,product_id
WHERE price_datestart > now()
GROUP BY price_productid,price_datestart,price_price,product_text,product_id
ORDER BY price_datestart ASC

It will give me a result, but two rows and I need one.

You need to use an aggregate function when you use group by. — Arpit Agrawal
– Arpit Agrawal, Commented Sep 19, 2013 at 9:41
You need to supply some more information. What price are you expecting to obtain, given that both dates are in the future? — user1596371
– user1596371, Commented Sep 19, 2013 at 9:44
Sorry. Let's just say date is 2013-10-05. So, the price started on 2013-10-01. ( 99$ = — Cherche
– Cherche, Commented Sep 19, 2013 at 9:47

roman · Accepted Answer · 2013-09-19 10:24:11Z

3

Use distinct on syntax. If you want current price:

select distinct on (p.productid)
    p.productid, pr.product_text, p.price, p.datestart
from Price as p
    left outer join Product as pr on pr.productid = p.productid
where p.datestart <= now()
order by p.productid, p.datestart desc

sql fiddle demo

edited Sep 19, 2013 at 10:24

answered Sep 19, 2013 at 9:46

roman

118k30 gold badges205 silver badges209 bronze badges

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

Comments

user1596371 · Accepted Answer · 2013-09-19 09:55:45Z

0

You have a few problems, but GROUP BY is not one of them.

First, although you have a datestart you don't have a dateend. I'd change datestart to be a daterange, for example:

CREATE TABLE product
( 
  product_id int
 ,product_text text
);

CREATE TABLE price
(
  price_productid int
 ,price_daterange TSRANGE
 ,price_price     NUMERIC(10,2)
);

The TSRANGE allows you to set up validity of your price over a given range, for example:

INSERT INTO product VALUES(1, 'phone');
INSERT INTO price VALUES(1, '[2013-08-01 00:00:00,2013-10-01 00:00:00)', 199);
INSERT INTO price VALUES(1, '[2013-10-01 00:00:00,2013-12-01 00:00:00)', 99);
INSERT INTO price VALUES(1, '[2013-12-01 00:00:00,)', 75);

And that makes your SELECT much more simple, for example:

SELECT price_productid,price_daterange,price_price,product_text,product_id
FROM product, price
WHERE price_daterange @> now()::timestamp
AND product_id = price_productid

This also has the benefit of allowing you to query for any arbitrary time by swapping out now() for another date.

You should read up on ranges in PostgresQL as they are very powerful. The example above is not complete in that it should also have indices on price_daterange to ensure that you do not have overlaps for any product.

SQL fiddle with above solution

answered Sep 19, 2013 at 9:55

user1596371

4 Comments

Cherche Over a year ago

Of course I dont have a date_end ;) The price start one day... I don't know when it end ;) It just the "New price". But you're right With this information, query may be easier

roman Over a year ago

Good use of ranges, but actually, in this task you don't need range - you can get most recent price easily with distinct on. Also, ig you would store ranges, you have to keep data consistent or you could have gaps

user1596371 Over a year ago

If you are keeping multiple prices for products at different times you really should keep track of when the price stops being valid. Pulling it from the next/last row is inefficient and error-prone. @Roman yes you need constraints but that's basic data hygiene, and the end result gives you lots of power to search for other info rather than just the current price. Might not be necessary, though.

roman Over a year ago

actually I'm using this types of query all the time, usually you don't need date when price stops being valid, you have you date, and you just need to get closest one price where price_date <= date. If you have proper indexes this very fast operation

Collectives™ on Stack Overflow

Postgres: GROUP BY several column

2 Answers 2

Comments

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related