# Incremental Graph per Day

• Aug 14, 2012, 09:48
K. Wolfe
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?
• Aug 14, 2012, 10:01
r937
Quote:

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
• Aug 14, 2012, 10:39
K. Wolfe
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.
• Aug 14, 2012, 11:54
r937
Quote:

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
• Aug 14, 2012, 12:03
K. Wolfe
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.