sorting on sums

[HomerJS]

I'm trying to list the top customers by an amount field. I need to calculate the amount field before I can sort it. I can total up the amount field. The problem I'm having is figuring out a way to sort by the totaled amount.

I am doing this in a web page (using InterDev) so I need something that can have values passed to it so I can select records by a certain date range, etc.

example . . .
Table:
Cust# Amt
1 10.00
2 7.50
2 20.00
1 9.50

Result desired:
Cust# Amt
2 27.50
1 19.50

If this is clear as mud let me know and I will try to explain more.

 

[foxdev]

[tt]select CustNum, sum(amt) as Amount
from MyTable
group by CustNum
order by sum(amt) desc [/tt]
Robert Bradley
Sr. DBA, some big company
cheap prints and oil paintings:

http://www.lowcostprints.com

 

[HomerJS]

Ok, that worked great. Now how do I use that sum value inside of InterDev? InterDev doesn't like the use of "sum(Amt)".

 

[foxdev]

I'm not an InterDev user, but the result set that you should get back should have that column labeled "Amount", since in the Select statement we gave that result column a name: ...sum(amt) as Amount... Robert Bradley
Sr. DBA, some big company
cheap prints and oil paintings:

http://www.lowcostprints.com