asked on

How to create a SQL SERVER view using Max and groping by..

I need to create a view. Data looks like below.

YEAR UNITS ACCOUNT#
2001 200 abc
2005 100 dvf
2003 50 aaa

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..

arbert

It's an aggregation query--you have to either group by everything, or include everything in an aggregate function.

If units is numeric--do this:

CREATE view vw_test
as select MAX(year), account#, sum(units)
FROM table_name
GROUP BY account#

if units is alpha, do this:

CREATE view vw_test
as select MAX(year), account#, max(units)
FROM table_name
GROUP BY account#

Do you always only have on unit per year?

Brett

ASKER CERTIFIED SOLUTION

Lowfatspread

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

walterecook

No comment has been added lately, so it's time to clean up this TA.
I will leave the following recommendation for this question in the Cleanup topic area:

Accept: Lowfatspread {http:#8194925}

Please leave any comments here within the next four days.
PLEASE DO NOT ACCEPT THIS COMMENT AS AN ANSWER!

walterecook
EE Cleanup Volunteer