2

I need to get a set of total transactions over time on a weekly interval. Essentially I need a total-transactions-to-date column. When I group by WEEK(Date) I get the amount of transactions for that week, but need to also get the transactions from any time before that week as well.

So let's say I have a table like this:

TransactionID  | Date
---------------|-----------
             1 | 8-04-2014
             2 | 8-05-2014
             3 | 8-18-2014
             4 | 8-18-2014
             5 | 8-20-2014

I want to have a select statement that would provide me with something like

TotalTransactionsToDate | Week | Year
------------------------|------|------
                      2 |    1 | 2014
                      5 |    3 | 2014

I am using MySql 5.5.38

0

3 Answers 3

2

What you want is called the cumulative sum, you can do something like:

create table transactions (transactionid int, d date);
insert into transactions (transactionid, d) 
    values (1, '2014-08-04'),(2,'2014-08-05'), (3, '2014-08-18')
         , (4, '2014-08-18'), (5,'2014-08-20');

select x.y, x.w,  count(1) 
from ( 
   select distinct year(d) as y, week(d) as w 
   from transactions
) as x 
join transactions y 
    on year(y.d) < x.y
    or ( year(y.d) = x.y
     and week(y.d) <= x.w ) 
group by x.y, x.w;  

+------+------+----------+
| y    | w    | count(1) |
+------+------+----------+
| 2014 |   31 |        2 |
| 2014 |   33 |        5 |
+------+------+----------+

I did not see your additional request for 2 2 for 2014. You can do that by replacing:

select distinct year(d) as y, week(d) as w 
from transactions 

...with an expression that creates the whole domain for weeks. It is often a good idea to create a calendar table that you can use to join against to get reports for missing values etc.

0
2

To get the basic data you need an aggregation:

select 1 + floor(datediff(date, mind) / 7) as week,
       year(date) as year,
       count(*) as num
from atable t cross join
     (select min(date) as mind
      from atable
     ) td
group by 1 + floor(datediff(date, mind) / 7),
         year(date)

You can extend this using variables to get the cumulative sum:

select week, year, num, (@cum := @cum + num) as cum
from (select 1 + floor(datediff(date, mind) / 7) as week,
             year(date) as year,
             count(*) as num
      from atable t cross join
           (select min(date) as mind
            from atable
           ) td
      group by 1 + floor(datediff(date, mind) / 7),
               year(date)
     ) x cross join
     (select @cum := 0) vars
order by year, week;
1

Maybe you can group it by the most recent Monday of the Date

SELECT COUNT(1) Transactions,WEEK(monday) Week,YEAR(monday) Year
(
    SELECT (Date - INTERVAL WEEKDAY(Date) DAY) monday
    FROM mytable WHERE Date >= MAKEDATE(YEAR(NOW()),1)
) A GROUP BY YEAR(monday),WEEK(monday);

NOTE: The WHERE clause in the subquery collects everything from the first of the Year. You can use whatever WHERE clause is appropriate.

If I understand you correctly, you want to rollup each week with the sum of all weeks prior to a date like a spreadsheet. You would have to set up a user-defined variable and perform the iteration and summation

SET @S = 0;
SELECT
    (@S:=@S+Transactions) TotalTransactionsToDate,
    Week,Year
FROM
(
    SELECT COUNT(1) Transactions,WEEK(monday) Week,YEAR(monday) Year
    (
        SELECT (Date - INTERVAL WEEKDAY(Date) DAY) monday
        FROM mytable WHERE Date >= MAKEDATE(YEAR(NOW()),1)
    ) A GROUP BY YEAR(monday),WEEK(monday)
) AA;

Give it a Try !!!

0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.