Post reply

Cumulative Group By

  • February 9, 2004 at 4:04 am

    #107528

    Dear Forum,

    I have sales figures that I want to group into months cumulatively.

    Is there a simple way to use "group by" to produce cumulative total sales figures throughout the year, i.e. from April to May, then April to June, April to July and so on...?

    Thanks, Bill

     

     

  • February 9, 2004 at 5:56 am

    #493231

    Hi Bill,

    I think , u must describe ur database design first.

    i.e. how are u storing ur month in database table.

    Nitin

  • February 9, 2004 at 6:18 am

    #493234

    CREATE TABLE calendar (

    CalDate datetime PRIMARY KEY,

    CalYear int,

    CalMonth int )

    INSERT INTO calendar values ('2003-04-01',2003,4)

    INSERT INTO calendar values ('2003-05-01',2003,5)

    INSERT INTO calendar values ('2003-06-01',2003,6)

    INSERT INTO calendar values ('2003-07-01',2003,7)

    etc

    SELECT c.CalYear,

     c.CalMonth,

     SUM(ISNULL(a.sales,0)) AS 'Total Sales'

    FROM calendar c

     LEFT OUTER JOIN

    a

      ON a.[date] < DATEADD(month,1,c.CalDate)

    WHERE c.CalDate >= '2003-04-01'

    AND c.CalDate <= '2004-03-31'

    GROUP BY c.CalYear,c.CalMonth

    Far away is close at hand in the images of elsewhere.
    Anon.

  • February 9, 2004 at 9:50 am

    #493275

    Thanks David,

    This looks like what I'm after. You're solution is very comprehensive. I can't get it to work though as I have probably misunderstood the principle.

    My problem is simpler than your suggestion, and these two tables describe it (I should have put this first, sorry).

    --This is an dummy table like my sales:

    CREATE TABLE sales (CalMonth int, cash money )

    INSERT INTO sales values (1,23.4)

    INSERT INTO sales values (1,234.98)

    INSERT INTO sales values (2,23.23)

    INSERT INTO sales values (2,234.44)

    INSERT INTO sales values (3,678.88)

    INSERT INTO sales values (4,234.87)

    INSERT INTO sales values (5,989.98)

    INSERT INTO sales values (6,345.56)

    INSERT INTO sales values (7,423.98)

    INSERT INTO sales values (8,456.89)

    INSERT INTO sales values (9,986.90)

    INSERT INTO sales values (10,435.89)

    INSERT INTO sales values (11,345.87)

    INSERT INTO sales values (12,765.98)

    --This is the calendar table you suggest:

    CREATE TABLE calendar (CalMonth int )

    INSERT INTO calendar values (1)

    INSERT INTO calendar values (2)

    INSERT INTO calendar values (3)

    INSERT INTO calendar values (4)

    INSERT INTO calendar values (5)

    INSERT INTO calendar values (6)

    INSERT INTO calendar values (7)

    INSERT INTO calendar values (8)

    INSERT INTO calendar values (9)

    INSERT INTO calendar values (10)

    INSERT INTO calendar values (11)

    INSERT INTO calendar values (12)

    --And this is my interpretation of your solution:

    SELECT  dbo.sales.CalMonth, SUM(dbo.sales.cash) AS [Total Sales]

    FROM  dbo.sales LEFT OUTER JOIN

             dbo.calendar ON dbo.sales.CalMonth < dbo.calendar.CalMonth

    GROUP BY dbo.sales.CalMonth

    Can you see where I've gone wrong? I'm after the cumulative sum of cash at each month (i.e. 1 to 1, then 1 to 2, then 1 to 3..etc..)

    Thanks, Bill

  • February 9, 2004 at 10:59 am

    #493281

    OK I did this without knowing your table defs. The principle is based on that you may have data missing for certain months but want to show those months with zero value. To do this you need a base table containing all the months required (a year in your case) plus I used dates without knowing your data. Against this base table you left outer join your data where the month of your data is less than or equal (note equal) and sum the data (you must use ISNULL because of missing data). Your example joined the other way round. So using your new tables you would write the query as

    SELECT c.CalMonth,SUM(ISNULL(s.cash,0)) AS [Total Sales]

    FROM calendar c

    LEFT OUTER JOIN sales s

    ON s.CalMonth <= c.CalMonth

    GROUP BY c.CalMonth

    ORDER BY c.CalMonth

    Now if your data is as per your dummy table, ie there is at least one row per month then you can achieve the same result without the calendar table by using a derived table instead, thus

    SELECT c.CalMonth,SUM(s.cash) AS [Total Sales]

    FROM (SELECT DISTINCT CalMonth FROM sales) c

    LEFT OUTER JOIN sales s

    ON s.CalMonth <= c.CalMonth

    GROUP BY c.CalMonth

    ORDER BY c.CalMonth

    Far away is close at hand in the images of elsewhere.
    Anon.

  • February 10, 2004 at 3:15 am

    #493349

    David,

    Many, many thanks. I used your final suggestion. 

    It's such an elegant solution compared to what I was using.

    Cheers, Bill

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply