SitePoint Sponsor

User Tag List

Results 1 to 5 of 5
  1. #1
    Always A Novice bronze trophy
    K. Wolfe's Avatar
    Join Date
    Nov 2003
    Location
    Columbus, OH
    Posts
    2,177
    Mentioned
    63 Post(s)
    Tagged
    2 Thread(s)

    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. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,215
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by K. Wolfe View Post
    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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    Always A Novice bronze trophy
    K. Wolfe's Avatar
    Join Date
    Nov 2003
    Location
    Columbus, OH
    Posts
    2,177
    Mentioned
    63 Post(s)
    Tagged
    2 Thread(s)
    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. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,215
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by K. Wolfe View Post
    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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  5. #5
    Always A Novice bronze trophy
    K. Wolfe's Avatar
    Join Date
    Nov 2003
    Location
    Columbus, OH
    Posts
    2,177
    Mentioned
    63 Post(s)
    Tagged
    2 Thread(s)
    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.


Bookmarks

Posting Permissions

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