Thread: VIEWs with aggregate functions

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


--
Paul Makepeace .............................. http://paulm.com/inchoate/

"If they asked us nicely, then we wouldnt be as happy with it."
   -- http://paulm.com/toys/surrealism/

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

Paul Makepeace <postgresql.org@paulm.com> writes:
>   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.

You can certainly select on bar_name, but you can't select on bar_date
because the view doesn't expose bar_date, only some statistics about
it.  I suppose that you want the WHERE clause to filter the bar_data
rows before the aggregation happens (is that correct??) and you just
can't do that with a view.  If it worked like that then the WHERE clause
would not simply limit the set of rows returned by the view but actually
change some of the values in those rows, and that's totally contrary to
the SQL worldview.

I think you're stuck with writing it out as one big query :-(

            regards, tom lane

Je 2004-10-14 05:09:16 +0100, Tom Lane skribis:
> Paul Makepeace <postgresql.org@paulm.com> writes:
> >   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.
>
> You can certainly select on bar_name, but you can't select on bar_date
> because the view doesn't expose bar_date, only some statistics about
> it.  I suppose that you want the WHERE clause to filter the bar_data
> rows before the aggregation happens (is that correct??) and you just
> can't do that with a view.

Yes, that's right, that is indeed what I was after.

>                             If it worked like that then the WHERE clause
> would not simply limit the set of rows returned by the view but actually
> change some of the values in those rows, and that's totally contrary to
> the SQL worldview.
>
> I think you're stuck with writing it out as one big query :-(

OK thanks for the confirmation - I suspected that was the case but
wanted to be sure!

Cheers, Paul

>             regards, tom lane

--
Paul Makepeace .............................. http://paulm.com/inchoate/

"What is the origin of happiness? Badly spelt."
   -- http://paulm.com/toys/surrealism/