Skip site navigation (1) Skip section navigation (2)

Re: VIEWs with aggregate functions

From: Ron St-Pierre <rstpierre(at)syscor(dot)com>
To: Paul Makepeace <postgresql(dot)org(at)paulm(dot)com>,pgsql-novice <pgsql-novice(at)postgresql(dot)org>
Subject: Re: VIEWs with aggregate functions
Date: 2004-10-13 23:01:13
Message-ID: 416DB3B9.20405@syscor.com (view raw or flat)
Thread:
Lists: pgsql-novice
Paul Makepeace wrote:

>I'm probably missing something obvious here but I can't quite see it.
>Given,
>
>  CREATE VIEW weekly_bar_info AS
>     SELECT bar_name,
>            MIN(bar_date) AS earliest_date, MAX(bar_date) AS latest_date,
>            MIN(bar) as min_bar, MAX(bar) as max_bar
>       FROM bar_data
>      WHERE valid_weekly='t'
>   GROUP BY bar_name
>
>What I'd like to be able do is WHERE bar_name = ? AND bar_date >= ?
>i.e. select over a date range but that isn't directly possible with the
>VIEW as is.
>
>Is there some way of achieving this?
>
>(The reason for the view is that that set of aggregate functions is
>appearing a few times in related queries, and that perl's Class::DBI is
>less of a headache with VIEWs.)
>
>Paul
>  
>
Can't you just SELECT * FROM weekly_bar_info WHERE  bar_name = ? AND 
earliest_date >= ?

or elimate the view and query directly from the table:

SELECT bar_name,
            bar_date,
            MIN(bar) as min_bar, MAX(bar) as max_bar
       FROM bar_data
      WHERE valid_weekly='t' AND bar_name = ? AND bar_date BETWEEN ? AND ? 
   GROUP BY bar_name


Ron


In response to

pgsql-novice by date

Next:From: Ron St-PierreDate: 2004-10-13 23:06:32
Subject: Re: Data Grid
Previous:From: Paul MakepeaceDate: 2004-10-13 22:39:42
Subject: VIEWs with aggregate functions

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group