I am wondering the best way to add up some figures for each month of the year so far and then print the information.
My table currently looks like this Table name: payouts
Columns are date | paid
I then want it to add all the numbers up in the paid column for each month and print like this
January : 100,000
February : 2,300
e.t.c
is this possible?
You can use something with sum and grouping by the month.
select month, sum(amount) from `data_values` group by month
See it in action http://sqlfiddle.com/#!9/0bd27/2
create table `data_values` (month varchar(50), amount decimal(18,2));
insert into `data_values` (month, amount) values ('January', 100),
('January', 50),
('Februari', 100),
('March', 100),
('April', 100),
('May', 100),
('May', 50),
('May', 25);
select month, sum(amount) from `data_values` group by month
month sum(amount)
April 100
Februari 100
January 150
March 100
May 175
Now if you have an actual date field in your table you can use monthname() to get the grouping you want
See it in action http://sqlfiddle.com/#!9/09a1d6/1
create table `data_values` (buy_date date, amount decimal(18,2));
insert into `data_values` (buy_date, amount) values ('2008-01-01', 100),
('2008-01-14', 50),
('2008-02-15', 100),
('2008-03-01', 100),
('2008-04-01', 100),
('2008-05-02', 100),
('2008-05-10', 50),
('2008-05-22', 25);
select monthname(buy_date), sum(amount) from `data_values` group by monthname(buy_date)
monthname(buy_date) sum(amount)
April 100
February 100
January 150
March 100
May 175
You might want to add an extra group by clause year(pay_date) so it doesn't mix the years together. Unless that's what you want. See http://sqlfiddle.com/#!9/9acc82/2 select year(buy_date), monthname(buy_date), sum(amount) from data_values group by year(buy_date), monthname(buy_date)