Re: VIEWs with aggregate functions

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