sql need help on query

tralala · July 1st, 2011, 01:45 PM

8rb,6rb,3rb,5rb,4rb

hello everybody...
i do need help about sql query..
this is the problem, for example i have table like this:

no | name |sale
--------------
1 doni 5000
2 jack 3000
3 owen 4000
4 luiz 8000
5 dinho 6000

and then i need to find peoples that give 80% of total sale??
total sale is 26,000. so 80% of it should hold value about 20,800.
so the result should look like this:
no | name |sale
--------------
1 doni 8000
2 jack 6000
3 owen 5000
4 luiz 4000

total of that is 23,000, and that meet the condition.
i'm trying to limit condition based on 80% of total but it doesnt work...

select * from table where sum(sale) < 0.8 * sum(sale) order by sale desc

i hope that query will stop when total is still 80% of total_sale.
but it doesnot work...
i really want to know about how to solve this problem...
i do hope every body can help me...
thanks before...

melvik · July 2nd, 2011, 09:22 AM

well there r wasy to achieve that result although i dont know y u gonna do it
anyway, first thing comes to my mind is Cursor & function base
I myself dont recommend cursor for its performance, & also solve it simply
lets go with function
(I used ur field names)

first create a function

Code:

CREATE FUNCTION [dbo].[GetTotalSale] (@no int, @Sum int) RETURNS int AS BEGIN return (SELECT @Sum+SUM(sale) from aaaa WHERE no <= @no)
END

then:

Code:

DECLARE @D bigint
SET @D = 0

SELECT *
FROM (
	select * ,dbo.GetTotalSale(no, @D) Sm
	from table) Tb
where Sm < 26000

HTH, let me now about it