• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 468
  • Last Modified:

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..
0
nelum_lk
Asked:
nelum_lk
1 Solution
 
arbertCommented:
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
0
 
LowfatspreadCommented:
CREATE VIEW LatestYear
 as Select Data.Account#,Data.Year,Data.Units
      From (select Max(year) as Year,account#
              from table_name
             group by Account#) as LatestYear
     inner join Table_name as Data
        on Data.Account#=LatestYear.Account#
       and Data.Year = LatestYear.Year


if you want to retrieve the data in the latestyear for an
account , otherwise you'll have to sum the units
or whatever is relevant to produce an aggregation in the original group...

hth
       
0
 
walterecookCommented:
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
0

Featured Post

Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now