How to count data within date intervals

Question

I want to make a graph chart from highcharts that count how much products have been sold from past 12 months.

For example: today is Jan, 7th

assume my sql table like this:

DATE           SOLD
feb-6           5
feb-8           7
mar-10          15
mar-30          1
apr-1           6
and so on..

so the sql must produce like this: feb = 12, mar = 16, apr = .... and so on until this month. anybody can help me? coz i have no idea how to write the correct sql code.

this is my javascript:

$(document).ready(function() {
    options = {
    chart: {renderTo: 'chart', type: 'line', marginRight: 130, marginBottom: 25},
    credits: {enabled: false},
    title: {text: 'Sales Performance', x: -20},
    xAxis: {categories: [{}]},
    yAxis: {title: {text: 'sold'}, plotLines: [{value: 0,width: 1,color: '#808080'}]},
    legend: {layout: 'vertical', align: 'right', verticalAlign: 'top', x: -10, y: 100, borderWidth: 0},
    tooltip: {
        formatter: function() {
            var s = '<b>'+ this.x +'</b>';
            $.each(this.points, function(i, point) {
                s += '<br/>'+point.series.name+': '+point.y;
            });
            return s;
        },
        shared: true
    },
    series: [{},{}]
};
$.ajax({
    url: "generate_graph",
    data: 'date='+dateText,
    type: 'post',
    dataType: "json",
    success: function(data){
        options.xAxis.categories = data.categories;
            options.series[0].name = data.name;
            options.series[0].data = data.value;
        var chart = new Highcharts.Chart(options);          
    }
    });
})

do you need sql query to do it or need to do it in javascript? how are you loading data using just javascript? — DevelopmentIsMyPassion, Jan 07 '14 at 08:29
I need sql query to be generated as json. I got the php from here http://blueflame-software.com/blog/pass-date-parameter-to-highcharts-line-chart/ but I need to edit the sql code to suit my needs — user3106988, Jan 07 '14 at 08:33
you may whant to check this : [MySQL Query GROUP BY day / month / year][1] Answer [GROUP BY YEAR(record_date), MONTH(record_date)][2] [1]: http://stackoverflow.com/questions/508791/mysql-query-group-by-day-month-year [2]: http://stackoverflow.com/a/508806/3148693 — Melon, Jan 07 '14 at 08:43

MrSimpleMind · Accepted Answer · 2014-01-07T08:48:44.110

1

I m just answering the sql query:

select
  month(solddate), 
  sum(sold)
from 
  selling
where solddate between date_sub(now(), interval 1 year) and now()
group by month(solddate);

see SQLFIDDLE

edited Jan 07 '14 at 08:48

answered Jan 07 '14 at 08:43

MrSimpleMind

7,890
3
40
45

score 1 · Answer 2 · edited May 23 '17 at 12:28

1

you may whant to check this : MySQL Query GROUP BY day / month / year

Answer:

GROUP BY YEAR(record_date), MONTH(record_date)

edited May 23 '17 at 12:28

Community

1
1

answered Jan 07 '14 at 08:46

Melon

874
8
17

How to count data within date intervals

2 Answers2