How to select amounts grouped by date?

Question

MySQL

There is for example a table with sum and date columns with date in unix format.
I need to get the sum of the sum field to a certain date at the output.
For example:
until January 5, the sum of all rows sum = 12000
until January 6, the sum of all rows sum = 15000

And so on
This is the output for statistics. I hope I explained clearly

Answer 1

If the dates are far from one, then you can do this

select 
`sum( if( `date`<'2012-01-01',sum20120101,0) ) as sum20120101 ,
`sum( if( `date`<'2012-01-02',sum20120102,0) ) as sum20120102 
from table
where `date`<'2012-01-02'

Answer 2

one request is very "expensive" :( it's
better to make a script that will do every day

insert into stat_table select date_sub(current_date, 1), sum(sum) from table where date < current_date;

create table stat_table (
 to_day date,
 big_sum int,
 primary key(to_day)
)

or if you really want to do everything dynamically, then you can try this beauty:

set @prev_day_sum:=0;
select
    day, 
    @prev_day_sum:=d_sum + @prev_day_sum as sum_from_month_begin, 
    d_sum 
from
    (select 
        date(date) as day, 
        sum(sum) as d_sum
    from 
        table 
    group by 
        day;
    ) as day_sum; 

day - day
sum_from_month_begin - sum from the beginning of the month
d_sum - for a specific day

Answer 3

If there is only one date, then this is how you can:

   select sum(`sum`) from `table` group by greatest(`day`, '2012-01-01')

All lines before the specified date will be summed up, and after it and with it go separately.

Answer 4

select
date_format( from_unixtime(UNIX_TIME_COLUMN), '%Y-%m-%d'),
sum(SUM_COLUMN)
from TABLE_NAME
group by 1