2

I have the following table.

DATE  | AMT
10/10 | 300
12/10 | 300
01/11 | 200
03/11 | 100

How do I get the monthly total? A result like - 

DATE | TOT
1010 | 300
1110 | 300
1210 | 600 
0111 | 800
0211 | 800
0311 | 900

A sql statement like

SELECT SUM(AMT) FROM TABLE1 WHERE DATE BETWEEN '1010' AND '0111'

would result in the 800 for 0111 but...

NOTE There is not a date restriction. which is my dilemma. How do I populate this column without doing a loop for all dates and have the missing months displayed as well?

pilcrow
  • 56,591
  • 13
  • 94
  • 135
MrM
  • 21,709
  • 30
  • 113
  • 139
  • 3
    DATE is VARCHAR or what type? – rkosegi Feb 21 '12 at 17:13
  • 1
    Hint: see "GROUP BY" in mysql manual http://dev.mysql.com/doc/refman/5.0/en/group-by-functions.html – bumperbox Feb 21 '12 at 17:13
  • @bumperbox - Doesn't deal with `and have the missing months displayed as well` – MatBailie Feb 21 '12 at 17:17
  • From the given example, where does this result come from? `1110 | 300` – Mosty Mostacho Feb 21 '12 at 17:17
  • @MostyMostacho - Thanks for your comment, it made me realise - This is a running total problem. The OP wants to do running totals, AND to 'fill in the gaps'. – MatBailie Feb 21 '12 at 17:20
  • 1
    Please don't name your columns after SQL types like `DATE`, even when the name of the type is an obvious choice for a column name. In the long term, the person-hours spent disambiguating type names and column names aren't worth it. – pilcrow Feb 21 '12 at 18:00

4 Answers4

2

To cater for missing months, create a template table to join against.

Think of it as caching. Rather than looping through and filling gaps, just have a calendar cached in your database.

You can even combine multiple calendars (start of month, start of week, bank holidays, working day, etc) all into one table, with a bunch of search flags and indexes.

You end up with something like...

SELECT
  calendar.date,
  SUM(data.amt)
FROM
  calendar
LEFT JOIN
  data
    ON  data.date >= calendar.date
    AND data.date <  calendar.date + INTERVAL 1 MONTH
WHERE
      calendar.date >= '20110101'
  AND calendar.date <  '20120101'
GROUP BY
  calendar.date

EDIT

I just noticed that the OP wants a running total.

This -is- possible in SQL but it is extremely inefficient. The reason being that the result from one month isn't used to calculate the following month. Instead the whole running-total has to be calculated again.

For this reason It is normally strongly recommended that you calculate the monthly total as above, then use your application to itterate through and make the running total values.

If you really must do it in SQL, it would be something like...

SELECT
  calendar.date,
  SUM(data.amt)
FROM
  calendar
LEFT JOIN
  data
    ON  data.date >= @yourFirstDate
    AND data.date <  calendar.date + INTERVAL 1 MONTH
WHERE
      calendar.date >= @yourFirstDate
  AND calendar.date <  @yourLastDate
GROUP BY
  calendar.date
MatBailie
  • 83,401
  • 18
  • 103
  • 137
  • 1
    This assumes you're using a DATETIME date type to store your dates. If you're using VARCHAR or something, I strongly recommend you change it. Deal with presentation when you select the data, not when you store the data. – MatBailie Feb 21 '12 at 17:18
0

the main problem is the and have the missing months displayed as well? I don't see how to do it with out an aux table containing the combination of month\year to be displayed:

create table table1(
date datetime,
amt int
)
insert into table1 values ('10/10/2010',100)
insert into table1 values ('12/12/2010',200)
insert into table1 values ('01/01/2011',50)
insert into table1 values ('03/03/2011',500)

truncate table #dates
create table #dates(
_month int,
_year int
)
insert into #dates values(10,2010)
insert into #dates values(11,2010) --missing month
insert into #dates values(12,2010)
insert into #dates values(01,2011)
insert into #dates values(02,2011)--missing month
insert into #dates values(03,2011)


select D._month, D._year, sum(amt)
from #dates D left join TABLE1 T on D._month=month(T.date) and D._year=year(T.date)
group by D._month, D._year
Diego
  • 34,802
  • 21
  • 91
  • 134
  • You can achieve this without an aux table by using a projected sequence on the fly. [How to generate a range of numbers in Mysql](http://stackoverflow.com/questions/186756/how-to-generate-a-range-of-numbers-in-mysql/187410#187410) – Pittsburgh DBA Feb 21 '12 at 17:35
  • "INSERT INTO myTable". Its the same thing...Can be used to populate the temp table yes, but the if the table is temporary or not doesn't matter, what matters is that he needs an auxiliary table to do what he wants – Diego Feb 21 '12 at 17:48
  • No argument there. I just wanted to point out that he can generate small sequences, especially like 1 or 2 years, at essentially zero cost, over a dynamic range, and not bother putting that into a table. – Pittsburgh DBA Feb 21 '12 at 17:54
0

You can also generate a range on the fly, pass its value as the interval to DATE_ADD, and basically project a sequence of month values.

As @Dems said, you need to have a correlated subquery calculate the running total, which will be very inefficient, because it will run a nested loop internally.

To see how to generate the sequence, check my post here: How to generate a range of numbers in Mysql

The end query should look something like this: (Incidentally, you should have a date column, not this varchar mess).

/*NOTE: This assumes a derived table (inline view) containing the sequence of date values and their corresponding TOT value*/
SELECT
  DATEVALUES.DateValue,
  (
  SELECT SUM(TABLE1.AMT) FROM TABLE1 WHERE TABLE1.DateValue <= DATEVALUES.DateValue)
  ) AS RunningSubTotal
FROM
  DATEVALUES

Or something like that.

Community
  • 1
  • 1
Pittsburgh DBA
  • 6,672
  • 2
  • 39
  • 68
-2
select sum(AMT) from TABLE1 group by Date
JiKra
  • 1,618
  • 3
  • 29
  • 45