Decile Report in SQL 2

[dpgirl]

I was wondering if it would be possible to create a decile report easily through SQL. Normally, I would export the data to excel.

Here's how to create the report. I have a table containing donation history for each member -- each row represents a different member. For example:

Donor ID Ttl Amt Donated
1342 $1000
525 $50
636 $250

To create the decile report, I would need to sort all records by the field "Ttl Amt Donated", in descending order. Then I would divide these records into 10 equal groups, keeping the sort order. So if there are 525 records in the table, then each group/decile would have approximately 52 or 53 records where the members who've given the most are in the top groups/deciles. Then I would sum the field "Ttl Amt Donated" for each of those groupings. The result would be as follows:


Decile 1 (top 10%): Total Amt=XXX, # Records=XXX
Decile 2 (2nd 10%): Total Amt=XXX, # Records=XXX
Decile 3 (3rd 10%): Total Amt=XXX, # Records=XXX
Decile 4 (4th 10%): Total Amt=XXX, # Records=XXX
etc. until Decile 10

 

[PeterAtKangasoft]

I am fairly confident that what you are trying to do cannot be done in SQL alone. You need just a little bit of code to do some processing after the SQL statement has been executed. You could write a stored proceedure or use another scripting/programming language to do what you are after.

What other technology is available to you?

Peter Sankauskas
Web Developer

http://www.kangasoft.com/

 

[gmmastros]

I once created a Stored Procedure that would calculate turn by turn driving instructions. It took 3 days to write it, and several more hours to debug it. Now, however, it works perfectly. My point is... 'cannot be done' usually means that it's difficult or you don't know how.

To create this report, I start off making a table variable so that I can create a 'RowId' column (Integer Identity(1,1)). I get the total number of records, and then can determine the decile that the record belongs to. Once I have the decile number in the record with the rest of the data, the final output is trivial.

In defense of the following code,I recognize that it is not optimized for performance. With only 525 records to deal with, performance isn't much of an issue.

Here's my solution to this problem.

Create Procedure sp_CreateDecileReport
As
SET NOCOUNT ON

Declare @TotalRecords Integer

Select @TotalRecords = Count(DonorId) From Donor

Declare @AllRecords
Table	(
	RowId Integer Identity(1,1),
	DecileNumber Integer,
	DonorId Integer,
	TotalAmount Float
	)

Insert
Into	@AllRecords
	(DonorId, TotalAmount)
Select	DonorId, TotalAmount
From	Donor
Order By TotalAmount DESC

Update 	@AllRecords
Set	DecileNumber = 1 + Convert(Integer, Convert(Float, RowId) / (Convert(Float, @TotalRecords) / 10.0))

Update @AllRecords
Set 	DecileNumber = 10 
Where	DecileNumber > 10

Select	DecileNumber,
	Count(DonorId) As NumberOfDonors,
	Sum(TotalAmount) As TotalDonations
From	@AllRecords
Group By DecileNumber
Order By DecileNumber

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[vongrunt]

Personally I'm using something like this:

select identity(int, 0, 1) as rank, DonorID, TotalAmount
into #blah
from Donor
order by TotalAmount desc

declare @cnt int; select @cnt = count(*) from #blah

select 1+10*rank/@cnt as decile, count(*) as rowcnt, sum(TotalAmount) as decileTotal, 
	min(TotalAmount) as minAmount, max(TotalAmount) as maxAmount
from #blah
group by 1+10*rank/@cnt
order by 1+10*rank/@cnt asc

drop table #blah

There are some differences - temp table instead of table var, SELECT INTO and identity(0, ...), no helper column - but principles look pretty much the same.

Btw. there are also some discussions about reliability of ordered INSERT on larger data sets.

------
"There's a man... He's bald and wears a short-sleeved shirt, and somehow he's very important to me. I think his name is Homer."
(Jack O'Neill, Stargate)