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’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:

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?

not me :slight_smile:

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
SELECT pledge_date 
     , SUM(pledge_amt) AS pledge_sum
  FROM pledges
    BY pledge_date  

then, here’s your cumulative query –

SELECT two.pledge_date
     , 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
    BY two.pledge_date

simple, yes? :slight_smile:

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.