CODEWITHSUNDEEP
mysqlgroup-by
Rippo
Rippo

Reputation: 22424

Group and sum data based on a day of the month

I have a reoccurring payment day of 14th of each month and want to group a subset of data by month/year and sum the sent column. For example for the given data:-

Table `Counter`
Id   Date         Sent 
1    10/04/2013     2      
2    11/04/2013     4      
3    15/04/2013     7 
4    10/05/2013     3      
5    14/05/2013     5      
6    15/05/2013     3 
7    16/05/2013     4

The output I want is something like:

From         Count
14/03/2013    6
14/04/2013    10
14/05/2013    12

I am not worried how the from column is formatted or if its easier to split into month/year as I can recreated a date from multiple columns in the GUI. So the output could easily just be:

FromMth   FromYr         Count
03         2013           6
04         2013          10
05         2013          12

or even

toMth       toYr         Count
04         2013           6
05         2013          10
06         2013          12

If the payment date is for example the 31st then the date comparison would need to be the last date of each month. I am also not worried about missing months in the result-set.

I will also turn this into a Stored procedure so that I can push in the the payment date and other filtered criteria. It is also worth mentioning that we can go across years.

Upvotes: 2

Views: 351

Answers (3)

Meherzad
Meherzad

Reputation: 8553

Try this query

select  
if(day(STR_TO_DATE(date, "%Y-%d-%m")) >= 14, 
   concat('14/', month(STR_TO_DATE(date, "%Y-%d-%m")), '/', year(STR_TO_DATE(date, "%Y-%d-%m"))) , 
   concat('14/', if ((month(STR_TO_DATE(date, "%Y-%d-%m")) - 1) = 0, 
                     concat('12/', year(STR_TO_DATE(date, "%Y-%d-%m")) - 1),
                     concat(month(STR_TO_DATE(date, "%Y-%d-%m"))-1,'/',year(STR_TO_DATE(date, "%Y-%d-%m")))
                    )
         )

  ) as fromDate, 
sum(sent)
from tbl
group by  fromDate

FIDDLE

|   FROMDATE | SUM(SENT) |
--------------------------
| 14/10/2013 |         3 |
| 14/12/2012 |         1 |
|  14/3/2013 |         6 |
|  14/4/2013 |        10 |
|  14/5/2013 |        12 |
|  14/9/2013 |         1 |

Upvotes: 2

Gordon Linoff
Gordon Linoff

Reputation: 1269963

I think the simplest way to do what you want is to just subtract 14 days rom the date and group by that month:

select date_format(date - 14, '%Y-%m'), sum(sent)
from counter
group by date_format(date - 14, '%Y-%m')

Upvotes: 1

Dmitrii Bychenko
Dmitrii Bychenko

Reputation: 186718

Pay date could be grouped by months and year separatedly

    select Sum(Sent) as "Count",
           Extract(Month from Date - 13) as FromMth,
           Extract(Year from Date - 13) as FromYr
      from Counter
  group by Extract(Year from Date - 13),
           Extract(Month from Date - 13)

Be careful, since field's name "Date" coninsides with the keyword "date" in ANSISQL

Upvotes: 1

Related Questions