Problems Sorting an SQL Query 1

[wxkeep]

I'm working on a database that stores sales information. I have a query on said table that does a lot of calculations based on region. All of my salespeople have an id, a region they are in, sales numbers, success ratios, etc. The query I have designed is more concerned with the regions than the individual salespeople. So, when it runs it totals up all the total sales per region, the average success ratio in each region, weighted commission rates, and average sales size. It works well, but now I'm trying to sort it an I run into problems. Here's a segment of the query:

SELECT [Dealer Name], (SUM([Sales]*.[Commission])/Sum([Sales])) AS WeightedAverage, Sum([Sales])/Sum(IIf([Sales]>0,1,0)) AS AvgContract, Sum(IIf([Sales]>0,1,0))/Sum(IIf([status]='Sold',1,0)) AS SuccessRation FROM qryDealer
GROUP BY [Dealer Name]
ORDER BY WeightedAverage DESC;

Which doesn't work. I've tried replacing 'WeightedAverage' with '(SUM([Sales]*.[Commission])/Sum([Sales]))' and I get overflow erros. It does the weighted average calculation correctly, I just can't seem to get it to sort by that calculation.

What simple thought did I miss? It's always something simple it seems. LOL

 

[cmmrfrds]

Did you try.

Order by ([Sales]*.[Commission])/Sum([Sales]) DESC

 

[PHV]

Use the ordinal position of the sort column:
ORDER BY 2 DESC

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[wxkeep]

I tried the order by 2 idea and I got the same overflow error. Trying cmmrfrds approach got me an error message that said it did not contain an aggregate function or something to that extent, so I guess we still haven't found it.

Any other ideas?

 

[PHV]

I suspect a Divide by zero ...

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[wxkeep]

Hmm - but if I take it out of the order by clause - and leave everything else alone - it works fine (without the sorting obviously) its only when I try to get it to sort that I get the overflow errors.

 

[wxkeep]

Okay - you're right. It's returning #Error on several rows of data due to no sales (hence it's dividing by a 0 for total sales) and therefore cannot sort. I need 0 error handling! Thanks so much for the tip!

 

[alvechurchdata]

What happens if you turn it into a MakeTable query then try to sort the table it produces?

Geoff Franklin

http://www.alvechurchdata.co.uk

 

[wxkeep]

I'm sure I'd get the same error - because the problem is - that in the weightedaverage field of the query data I have several fields that contain #error as their result. The error is a result as PHV suggested of dividing by zero. So, once i put in a little math to allow for the potential that a region produced 0 sales. IF it does - then don't do the division - can't have a weightedaverage of commission if there weren't any commissions. =)

 

[PHV]

SELECT [Dealer Name], IIf(Sum([Sales])<>0,Sum([Sales]*[Commission])/Sum([Sales]),0) AS WeightedAverage, ...

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[cmmrfrds]

You can use an Alias if you create a temporary table and pass it back to an outer select statement which will recognize the alias.

Select *
From
(
SELECT [Dealer Name], (SUM([Sales]*.[Commission])/Sum([Sales])) AS WeightedAverage, Sum([Sales])/Sum(IIf([Sales]>0,1,0)) AS AvgContract, Sum(IIf([Sales]>0,1,0))/Sum(IIf([status]='Sold',1,0)) AS SuccessRation FROM qryDealer
GROUP BY [Dealer Name]
)
ORDER BY WeightedAverage DESC;