Stumped :( - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Stumped :(

Author

Topic

Aerathi
Starting Member

14 Posts

Posted - 2006-04-24 : 15:46:34

Let's say I have a table like follows:


Order No    ItemNo    Amount    State
 1           1         50        AL
 1           2         100       AL
 1           3         65        AL
 2           1         100       PA
 2           2         45        PA

I need a query that gives me the Average sum of each order per state, and the total order count per state. This much I am able to get. But I also need a count of orders per state whose Amount sum is within a certain range. So for instance I need to sum up order number 1, and figure out which range it falls in, and count all the orders in the state that fall into that range. The problem is, with grouping by state, I cannot get the sum of each individual order, and if I add OrderNo to the group by clause, I no longer have it neatly grouped by state.

Basically my query looks something like this so far:


SELECT State, COUNT(distinct OrderNo),
     SUM(Amount) / Count(distinct OrderNo) AS Average
FROM Orders
GROUP BY State

Is it possible to also get a count of all the orders in each state who's Amount sum falls into certain ranges?

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2006-04-24 : 15:55:55

>>Is it possible to also get a count of all the orders in each state who's Amount sum falls into certain ranges?

I'm not sure if by "Amount sum" you mean "amount" or "sum(amount)" but since you say "in each state" I will assume you mean the count of "Amount" in a certain range.

Try this:


declare @lowRange int
	,@highRange int
select	@lowRange = 10
	,@highRange = 20

select	State
	,COUNT(distinct OrderNo)
	,SUM(Amount) / Count(distinct OrderNo) AS Average
	,sum(inRange) as CountInRange
from	(
	select	[Order No]
		,ItemNo    
		,Amount    
		,State
		,inRange = case when Amount between @lowRange and @highRange then 1 else 0 end
	FROM	Orders
	) a
GROUP BY State

Be One with the Optimizer
TG

Aerathi
Starting Member

14 Posts

Posted - 2006-04-24 : 16:44:56

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources