Group individual dates into seperate months

[Stephen]

Hi All.

I have table that contains a date field. There are many
records with different dates that go throughout the year.

I want to group the dates into months and have a column of
months and totals of data from the many records.

example data;
08/01/2004 31 10 1
09/01/2004 97 05 1
12/01/2004 40 01 1
13/02/2004 48 0 1
14/02/2004 27 0 1
15/03/2004 49 15 1
16/03/2004 59 5 1
19/03/2004 46 5 1

Required result
Jan 168 16 3
Feb 75 0 2
Mar 154 25 3

Many thanks

Stephen

 

[audreybmorin]

If you're using query builder, set the field value to
month([Date])
with the operation to grouping
(where Date is the name of the column containing your date)
Then add the fields you want to sum, and set the operation on those fields to sum.

In SQL, it would be something like
SELECT Month([DATE]), Sum(field1), Sum(field2)
FROM yourtable
GROUP BY Month([DATE]);

 

[audreybmorin]

Sorry if the message is posted twice - forums not working right this morning!

If you're using query builder, set the field value to
month([Date])
with the operation to grouping
(where Date is the name of the column containing your date)
Then add the fields you want to sum, and set the operation on those fields to sum.

In SQL, it would be something like
SELECT Month([DATE]), Sum(field1), Sum(field2)
FROM yourtable
GROUP BY Month([DATE]);

 

[audreybmorin]

I've thought of something else...

If you only group by Month(Date), you'll end up grouping January together for every year. You'll have to group by Year(Date), then Month(Date).