Having problems building a select list with an aggregate and Group by Clause

[westdh]

Having problems building a select list with an aggregate and Group by Clause

Using VWD and mssql server express 2008
Below I have listed two conditions one that works and one that does not. I have named them example 1 and 2
Example 1: A working Select List
-----------------------------------------------------------------------------------------------------
Example 1:

Select list
ALTER PROCEDURE dbo.getshippingdata
AS
	SELECT 
		a.UserID,
		u.FirstName,
		u.LastName,
		u.Email,
		a.curmth,
	              a.curamt,
		a.state,
		b.limit,
		b.period,
		result = CASE 
		WHEN ( a.curamt > b.limit) THEN 'Over State Limit'
		WHEN ( a.curamt < b.limit) THEN 'Within State Limit' ELSE 'Equals State Limit'
		END
	FROM CurOrder AS a JOIN Statelimits AS b ON a.state = b.state JOIN dbo.Users u ON   a.UserID = u.UserID Order by a.state

Example 1 results
--------------------------------------------------------------------------------------------------------------------------------------
User ID First Name Last Name Email Current Month Current Amount Ship to State Result Limit Period
1 Dennis West westdhXcharter.net May 1 Hawaii Within State Limit 3 Per Year
1 Dennis West westdhXcharter.net May 2 Idaho Equals State Limit 2 Per Month
1 Dennis West westdhXcharter.net May 2 Illinois Equals State Limit 2 Per Year
1 Dennis West westdhXcharter.net May 1 Iowa Within State Limit 2 Per Month
1 Dennis West westdhXcharter.net May 3 Iowa Over State Limit 2 Per Month
1 Dennis West westdhXcharter.net May 2 Oregon Within State Limit 1000 No Limit
1 Dennis West westdhXcharter.net May 2 Washington Equals State Limit 2 Per Year
1 Dennis West westdhXcharter.net May 2 West Virginia Equals State Limit 2 Per Month
--------------------------------------------------------------------------------------------------------------------------------------
Example 2: A Failing Select list
Below are the results when using the following Select list with group by clause and an aggregate SUM on the column a.curamt. This list compiles and I see that the sum(a.curamt) is summing the two rows for the state of Iowa which have a value of 1 and 3 for the two rows show a value of 4 -- see table 1 for values. But it is not grouping the two rows into one row.
Please Note: I have to include the Sum aggregate column in the group by in order to get this select list to even compile. If I remove the aggregate column a.curamt from the Group By Clause.
I get a Visual web developer 2008 Express edition error popup message stating that --
[Column 'CurOrder.curamt' is invalid in the select list because it is not contained in either an aggregate function or the Group By clause.]
--------------------------------------------------------------------------------------------------------------------------------------
Example 2:

Select list
ALTER PROCEDURE dbo.getshippingdata
AS
SELECT 
		a.UserID,
		u.FirstName,
		u.LastName,
		u.Email,
		a.curmth,
		SUM(a.curamt) OVER(PARTITION BY a.state) AS curamt,
		a.state,
		b.limit,
		b.period,
		result = CASE 
		WHEN ( a.curamt > b.limit) THEN 'Over State Limit'
		WHEN ( a.curamt < b.limit) THEN 'Within State Limit' ELSE 'Equals State Limit'
		END
		FROM CurOrder AS a JOIN Statelimits AS b ON a.state = b.state JOIN dbo.Users u ON a.UserID = u.UserID
	GROUP by
		a.UserID,
		u.FirstName,
		u.LastName,
		u.Email,
		a.curmth,
		a.curamt,
		a.state,
		b.state,
		b.limit,
		b.period,
		a.result
		Order by a.state

Example 2 results
--------------------------------------------------------------------------------------------------------------------------------------
User ID First Name Last Name Email Current Month Current Amount Ship to State Result Limit Period
1 Dennis West westdhXcharter.net May 1 Hawaii Within State Limit 3 Per Year
1 Dennis West westdhXcharter.net May 2 Idaho Equals State Limit 2 Per Month
1 Dennis West westdhXcharter.net May 2 Illinois Equals State Limit 2 Per Year
1 Dennis West westdhXcharter.net May 4 Iowa Within State Limit 2 Per Month
1 Dennis West westdhXcharter.net May 4 Iowa Over State Limit 2 Per Month
1 Dennis West westdhXcharter.net May 2 Oregon Within State Limit 1000 No Limit
1 Dennis West westdhXcharter.net May 2 Washington Equals State Limit 2 Per Year
1 Dennis West westdhXcharter.net May 2 West Virginia Equals State Limit 2 Per Month