So I don't have a need for this yet, but I got wondering how I might build it should the need arise (which i have a project coming up that will certainly need it). I was looking at kickstarter.com's mobile app screenshots today, and saw the common graph of total amount pledged by day. Aug 1st would show 100k, Aug 2 moves up to 101k, etc. So how would I prepare the data for a graph such as this with a data set in this form:
PLEDGE_DATE | PLEDGE_AMOUNT
08/01/2012 | 1000
08/01/2012 | 5000
08/02/2012 | 50
08/02/2012 | 500
08/02/2012 | 5848
08/02/2012 | 3838
08/02/2012 | 138
Who can beat r937 to the punch?
is your question related to how to pull the data out in a form suitable for graphing?
you'd need GROUP BY for that, and a theta self-join, i think, if you're looking for cumulative total
if just a total pledge by day, not cumulative, it's a lot simpler
So Yes I suppose it would obviously need a sum, grouped by day. But where am I getting the fact that aug 1 + aug 2 pledges equals my total for aug2, aug 3 would be the sum of aug 1-3, etc (assuming the first pledge was aug 1). I feel like I'd have to do a subquery to find the sum for that project where the pledge date <= each date in the range.
With the sample data provided, the graph would show aug 1sts sum at 6,000, and the point for aug 2nd would be at 16374.
yeah, you could, but subqueries are occasionally very poor performers
i prefer a theta self-join
first, create this view --
CREATE VIEW pledges_by_date AS
, SUM(pledge_amt) AS pledge_sum
then, here's your cumulative query --
, SUM(one.pledge_sum) AS pledge_cumulative
FROM pledges_by_date AS two
JOIN pledges_by_date AS one
ON one.pledge_date <= two.pledge_date
i'm guessing you're on sql server, so you could also use WITH syntax instead of the view
Ah, very clean and efficient. I'll be referring back to this in a few months I feel. I currently work with Oracle quite a bit, but I'll be using this on MySQL.
This topic is now archived. It is frozen and cannot be changed in any way.