March 23, 2003 at 11:47 am
I need to create a view. Data looks like below.
YEAR UNITS ACCOUNT#
2001 200abc
2005100 dvf
200350aaa
I need to find out the TOTAL for the most recent year only grouping by ACCOUN#.
when I create view ...
CREATE view vw_test
as select MAX(year), account#, units
FROM table_name
GROUP BY account#
it gives me an error telling that I need to group by UNITS as well. but it is not a want. Please help..
March 23, 2003 at 12:54 pm
You'll have to either use an aggregate function in the select, or add the column in the group by.
Andy
March 23, 2003 at 3:32 pm
Try this:
CREATE view vw_test
AS
SELECT year, account#, units
FROM table_name
WHERE year = (SELECT MAX(year) FROM table_name)
--
Chris Hedgate @ Apptus Technologies (http://www.apptus.se)
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply