Re: View with group by

Rudy,

Thanks for your clever reply. Unfortunately, your second assumption is invalid in our case -- we cannot guarantee that tax amounts will increase or even remain the same. Any added suggestions?

Also, to answer your first assumption, this table contains information from a legacy system, so we have no choice on the design.

Cheers,
Miguel Pascoe
Miguel.Pascoe_at_fmr.com

In article <936192710.762677624_at_news.pathcom.com>,   Rudy Fernandes <rferdy_at_pathcom.com> wrote:
> It appears that your table stores Sales for the Month, but tax for the
year to
> date. That's quite odd.
>
> However, on the assumption that there is some purpose being served
with that
> design and on a further important assumption that the tax value only
increases
> during a given year (i.e. Sales tax for a month always exceed
refunds), you
> could get your results with the following view
>
> create view vx as
> select emp, trtry, trunc(month/100) year, sum(sales) sales, max(tax)
tax
> from sales_tab
> group by emp, trtry, trunc(month/100);
>
> Rudy
>

Sent via Deja.com http://www.deja.com/
Share what you know. Learn what you don't. Received on Thu Sep 02 1999 - 07:39:25 CDT