From: | Janko Richter <jankorichter(at)yahoo(dot)de> |
---|---|
To: | colorob(at)tin(dot)it, pgsql-sql(at)postgresql(dot)org |
Subject: | Re: select |
Date: | 2003-10-09 16:58:42 |
Message-ID: | 3F8593C2.4080809@yahoo.de |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-sql |
roberto wrote:
> Dear friends,
> I have this table
>
> table work{
> day date,
> hour integer,
> }
>
> select * from work;
>
> date | text
> -----------
> 1-1-2003 1
> 1-1-2003 1
> 2-1-2003 5
> 3-1-2003 10
> 5-1-2003 15
>
> how can i obtain this?
>
> date | text
> -----------
> 1-1-2003 2
> 2-1-2003 5
> 3-1-2003 10
> 4-1-2003 null
> 5-1-2003 15
> 6-1-2003 null
>
>
First , you need a sequence of days. Just create
a function like this:
CREATE OR REPLACE FUNCTION dateseq ( DATE , DATE )
RETURNS SETOF DATE
LANGUAGE 'plpgsql'
AS '
DECLARE
bdate ALIAS FOR $1 ;
edate ALIAS FOR $2 ;
cdate DATE ;
BEGIN
cdate := bdate;
WHILE cdate <= edate LOOP
RETURN NEXT cdate ;
cdate := CAST ( cdate + interval ''1 day'' AS date );
END LOOP;
RETURN;
END;
';
The function is like a table/view , where the fist function argument
is the start date , the second argument is the end date.
Now try :
SELECT ds.day, sum(w.hour)
FROM dateseq( '2003-1-1', '2003-1-6' ) AS ds (day)
LEFT JOIN work w ON ds.day=w.day
GROUP BY ds.day;
Regards, Janko
--
Janko Richter
From | Date | Subject | |
---|---|---|---|
Next Message | Josh Berkus | 2003-10-09 17:14:09 | Re: Selecting rows as if they were columns? |
Previous Message | roberto | 2003-10-09 15:09:34 | select |