# Thread: Incremental Graph per Day

1. ## Incremental Graph per Day

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:

Code:
```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```
Mini competition:
Who can beat r937 to the punch?

2. Originally Posted by K. Wolfe
Mini competition:
Who can beat r937 to the punch?
not me

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

3. 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.

4. Originally Posted by K. Wolfe
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.
yeah, you could, but subqueries are occasionally very poor performers

i prefer a theta self-join

first, create this view --
Code:
```CREATE VIEW pledges_by_date AS
SELECT pledge_date
, SUM(pledge_amt) AS pledge_sum
FROM pledges
GROUP
BY pledge_date```
then, here's your cumulative query --
Code:
```SELECT two.pledge_date
, SUM(one.pledge_sum) AS pledge_cumulative
FROM pledges_by_date AS two
LEFT OUTER
JOIN pledges_by_date AS one
ON one.pledge_date <= two.pledge_date
GROUP
BY two.pledge_date```
simple, yes?

i'm guessing you're on sql server, so you could also use WITH syntax instead of the view

5. 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.

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•