Help creating a view with logic... 1

[Quickfix99]

I have a view which returns a list of records by year. What I would like to do is have one row returned in the view, even though there are two rows in the origin table. Here is an example of the data:

Current View Returns: (2 records)

Year GrossLand GrossImprovements
2002 0.00 5000.00
2002 1000.00 0.00

What I really want: (1 record)

Year GrossLand GrossImprovements
2002 1000.00 5000.00

Is this possible?

Thanks in advance,

M

 

[notadba]

Hi Quickfix,

I think we will need more details -
For each row will one column always be 0?
Did you want a sum of values for each year, max, min?

Yes it is possible!


<Witty caption goes here>

 

[SantaMufasa]

M,

It appears that you want a single row for each year that sums the values you posted. Although you have not disclosed the actual column names that you plan to use, here is SQL*Plus code that shows the type of thing you can/should do that produces the exact results you specify:

col a heading "Year" format a4
col b heading "GrossLand" format 99999999.99
col c heading "GrossImprovements" format 99999999.99
select year a, sum(grossland) b, sum(grossimps) c
  from your_table
 group by year;

Let us know if this produces what you wanted.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]

 

[Quickfix99]

Hi Mufasa,

Thanks for the tip. I will give it a try. Just a bit more informaton for you. There is actually another column I would need to group by as well as year:

ID Year GrossLand GrossImprovements
56 2002 0.00 5000.00
56 2002 1000.00 0.00
56 2004 2000.00 0.00
56 2004 0.00 3000.00

What I really want: (2 records)

ID Year GrossLand GrossImprovements
56 2002 1000.00 5000.00
56 2004 2000.00 3000.00

Thanks in advance,

Mark

 

[Dagon]

select id, year a, sum(grossland) b, sum(grossimps) c
  from your_table
 group by id, year;