Postgres Pro
Downloads
Home   >   mailing lists

Thread: SQL problem..

SQL problem..

From
"Bauhaus"
Date:
Hello,

I'm an Access/SQL novice and I have an sql problem:

I have the following table Price:

FuelID PriceDate   Price
LPG    1/05/2007   0,2
LPG    13/05/2007 0,21
SPS     2/05/2007   1,1
SPS     15/05/2007 1,08

And I have to make the following query:

FuelID PriceDate_from PriceDate_To Price
LPG    1/05/2007         13/05/2007     0,2
SPS     2/05/2007         15/05/2007    1,1
LPG    13/05/2007                              0,21
SPS     15/05/2007                             1,08

I tried this:

SELECT FuelID, min(FuelDate) AS Pricedate_from, max(FuelDate) AS
PriceDate_to FROM Price GROUP BY FuelID;

Problem is, when I put Price in the select, I get the error 'Price not part
of an aggregate function' :s
Eitherway, it doesnt work, I only have one FuelDate_from and one FuelDate_to
if I use min & max. While there should be several from...to... dates for a
particular fuel.

How can I solve this ?

Re: SQL problem..

From
Andreas Kretschmer
Date:
Bauhaus <niemandhier@pandora.be> schrieb:

> Hello,
>
> I'm an Access/SQL novice and I have an sql problem:
>
> I have the following table Price:
>
> FuelID PriceDate   Price
> LPG    1/05/2007   0,2
> LPG    13/05/2007 0,21
> SPS     2/05/2007   1,1
> SPS     15/05/2007 1,08
>
> And I have to make the following query:
>
> FuelID PriceDate_from PriceDate_To Price
> LPG    1/05/2007         13/05/2007     0,2
> SPS     2/05/2007         15/05/2007    1,1
> LPG    13/05/2007                              0,21
> SPS     15/05/2007                             1,08


> How can I solve this ?

There are different solutions, one of them:

write a function like this:

create or replace function price_list(OUT _id text, OUT _date_from date, out _date_to date, OUT _price numeric(10,2))
returnssetof record as $$ 
declare _row record;
        _old date;
        _old_id text;
begin
        _old := NULL;
        _old_id := NULL;
        for _row in select * from price order by fuel_id, price_date
desc loop
                if _old_id != _row.fuel_id then
                        _date_to := NULL;
                else
                        _date_to := _old;
                end if;
                _old_id := _row.fuel_id;
                _id := _row.fuel_id;
                _date_from := _row.price_date;
                -- _date_to := _old;
                _price := _row.price;
                _old := _row.price_date;
                return next ;
        end loop;
end;
$$ language plpgsql;


Test:

test=*# \d price
          Table "public.price"
   Column   |     Type      | Modifiers
------------+---------------+-----------
 fuel_id    | text          |
 price_date | date          |
 price      | numeric(10,2) |

test=*# select * from price ;
 fuel_id | price_date | price
---------+------------+-------
 LPG     | 2007-05-01 |  0.20
 LPG     | 2007-05-13 |  0.21
 SPS     | 2007-05-02 |  1.10
 SPS     | 2007-05-15 |  1.08
(4 rows)



test=*# select * from price_list() order by _id, _date_from asc;
 _id | _date_from |  _date_to  | _price
-----+------------+------------+--------
 LPG | 2007-05-01 | 2007-05-13 |   0.20
 LPG | 2007-05-13 |            |   0.21
 SPS | 2007-05-02 | 2007-05-15 |   1.10
 SPS | 2007-05-15 |            |   1.08
(4 rows)

Other solution:

Alter your table and add a new column for the end-valid-date and write a
trigger. This trigger updates your table for every new inserted record.
(insert the new inserted date_from as date_to into the last record)

Finaly a link for you:
http://www.rueping.info/doc/Andreas%20R&ping%20--%202D%20History.pdf


Andreas
--
Really, I'm not out to destroy Microsoft. That will just be a completely
unintentional side effect.                              (Linus Torvalds)
"If I was god, I would recompile penguin with --enable-fly."    (unknow)
Kaufbach, Saxony, Germany, Europe.              N 51.05082°, E 13.56889°

Re: SQL problem..

From
"Postgres User"
Date:
Your query won't work because there is no single Price associated with
a range of dates.  It doesn't make sense.

Do you mean to select AVG(Price)?

On 6/28/07, Bauhaus <niemandhier@pandora.be> wrote:
> Hello,
>
> I'm an Access/SQL novice and I have an sql problem:
>
> I have the following table Price:
>
> FuelID PriceDate   Price
> LPG    1/05/2007   0,2
> LPG    13/05/2007 0,21
> SPS     2/05/2007   1,1
> SPS     15/05/2007 1,08
>
> And I have to make the following query:
>
> FuelID PriceDate_from PriceDate_To Price
> LPG    1/05/2007         13/05/2007     0,2
> SPS     2/05/2007         15/05/2007    1,1
> LPG    13/05/2007                              0,21
> SPS     15/05/2007                             1,08
>
> I tried this:
>
> SELECT FuelID, min(FuelDate) AS Pricedate_from, max(FuelDate) AS
> PriceDate_to FROM Price GROUP BY FuelID;
>
> Problem is, when I put Price in the select, I get the error 'Price not part
> of an aggregate function' :s
> Eitherway, it doesnt work, I only have one FuelDate_from and one FuelDate_to
> if I use min & max. While there should be several from...to... dates for a
> particular fuel.
>
> How can I solve this ?
>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 4: Have you searched our list archives?
>
>               http://archives.postgresql.org/
>

Re: SQL problem..

From
"Bauhaus"
Date:
thnx !