FAQ
I have a table with sampledate, meter1, meter2...etc

I am trying to develop a function or query

where:

select sampledate, (meter1 - (select meter1 where sampledate = sampledate +
interval '1 days')) as consumption from mytable;



Any ideas how to make this work?


Mark King

Search Discussions

  • Oliveiros d'Azevedo Cristina at Apr 18, 2011 at 3:35 pm
    Hello!

    I fail to understand what you are attempting to do.
    What does your table represent and what output you expect to have..?

    To my best knowledge this (select meter1 where sampledate = sampledate + interval '1 days') is not valid SQL so could you please explain in english the info
    you want to obtain from your table?


    Best,
    Oliveiros

    ----- Original Message -----
    From: Mark King
    To: pgsql-novice@postgresql.org
    Sent: Monday, April 18, 2011 4:16 PM
    Subject: [NOVICE] can a function take a column name or is there another solution


    I have a table with sampledate, meter1, meter2...etc

    I am trying to develop a function or query

    where:

    select sampledate, (meter1 - (select meter1 where sampledate = sampledate + interval '1 days')) as consumption from mytable;



    Any ideas how to make this work?


    Mark King
  • Tom Lane at Apr 18, 2011 at 3:38 pm

    Mark King writes:
    I have a table with sampledate, meter1, meter2...etc
    I am trying to develop a function or query
    where:
    select sampledate, (meter1 - (select meter1 where sampledate = sampledate +
    interval '1 days')) as consumption from mytable;
    Any ideas how to make this work?
    I think what you're looking for is window functions, specifically the
    LEAD/LAG functions. You need Postgres >= 8.4 for those though.

    regards, tom lane
  • Mark King at Apr 18, 2011 at 3:44 pm
    The Lead/Lag functions are exactly what I am looking for.
    Thank you
    On Mon, Apr 18, 2011 at 11:37 AM, Tom Lane wrote:

    Mark King <fires10@gmail.com> writes:
    I have a table with sampledate, meter1, meter2...etc
    I am trying to develop a function or query
    where:
    select sampledate, (meter1 - (select meter1 where sampledate = sampledate +
    interval '1 days')) as consumption from mytable;
    Any ideas how to make this work?
    I think what you're looking for is window functions, specifically the
    LEAD/LAG functions. You need Postgres >= 8.4 for those though.

    regards, tom lane

Related Discussions

Discussion Navigation
viewthread | post
Discussion Overview
grouppgsql-novice @
categoriespostgresql
postedApr 18, '11 at 3:16p
activeApr 18, '11 at 3:44p
posts4
users3
websitepostgresql.org
irc#postgresql

People

Translate

site design / logo © 2022 Grokbase