|
| |
|
|
xgd
Posts: 59
Joined: 5/1/2007
Status: offline
 |
Top 'x' results - 7/16/2007 16:22:46
Greetings,
let's say I have a table with 2 fields, customer and amount.
customerA, 100.00
customerA, 200.00
customerB, 50.00
customerC, 250.00
customerD, 75.00
customerE, 30.00
customerE, 100.00
customerF, 100.00
customerG, 40.00
(Customers can repeat and there is never a set amount of them.)
I would like to see the TOP 5 Sums and then a Sum of the remaining
example:
customerA, 300.00
customerC, 250.00
customerE, 130.00
customerF, 100.00
customerD, 75.00
OTHERS, 90.00
Any help is always greatly appreciated.
Greg
|
|
|
|
rdouglass
Posts: 9280
From: Biddeford, ME USA
Status: offline
|
RE: Top 'x' results - 7/16/2007 16:36:53
What do you currently have? Are you using the DRW or straight ASP?
Do you have anything to start with?
_____________________________
Don't take you're eye off your final destination.
ASP Checkbox Function Tutorial.
| |
|
|
|
xgd
Posts: 59
Joined: 5/1/2007
Status: offline
|
RE: Top 'x' results - 7/16/2007 16:51:28
Guess that might help a little.
Using the DRW
I can get as for as the SELET TOP 5 .....
As in: SELECT TOP 5 Sum(amount) AS top5total, customer
FROM table
GROUP BY customer
ORDER BY Sum(amount) DESC , customer;
Then I would use:
<%=FP_FieldVal(fp_rs,"customer")%> <%=FP_FieldVal(fp_rs,"top5total")%>
for the results
Having trouble (As in I have no idea where to start) for the remaing total.
Thanks,
Greg
| |
|
|
|
xgd
Posts: 59
Joined: 5/1/2007
Status: offline
|
RE: Top 'x' results - 7/27/2007 11:09:28
I found a solution if anyone might be intrested.
SELECT SUM(AMOUNT), CUSTOMER FROM TABLE
WHERE CUSTOMER IN (
SELECT TOP 5 customer
FROM table
GROUP BY customer
ORDER BY Sum(amount) DESC , customer )
GROUP BY CUSTOMER
UNION
SELECT SUM(AMOUNT), 'OTHERS' as customer from table
WHERE CUSTOMER NOT IN (
SELECT TOP 5 customer
FROM table
GROUP BY customer
ORDER BY Sum(amount) DESC , customer )
Thanks,
--gd
< Message edited by xgd -- 7/27/2007 11:15:42 >
| |
|
 New Messages |
 No New Messages |
 Hot Topic w/ New Messages |
 Hot Topic w/o New Messages |
 Locked w/ New Messages |
 Locked w/o New Messages |
|
Post New Thread
Reply to Message
Post New Poll
Submit Vote
Delete My Own Post
Delete My Own Thread
Rate Posts
|
|
|
Printable Version 
