从当前年度的每个月的表格中添加数字

Question

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?

Answer 1

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)