CODEWITHSUNDEEP
mysqlsql
astroboy
astroboy

Reputation: 197

grouping data in SQL based on date manipulations

I've sample data here (date: yyyy-mm-dd format)

Headquarter     date       Sales       monthyear
    1        2020-10-30    1000         202010
    1        2020-10-31    500          202010
    1        2020-11-01    1000         202011
    1        2020-11-02    2000         202011
    1        2020-11-03    3000         202011
    1        2020-11-04    1000         202011
    1        2020-11-05    1000         202011

I have to sum all the sales values from 2nd of current month to 2nd of next month. Grouping by headquarter and monthyear. So if I run the query, based on current_date(), the sales value should be added to that month respectively.

To brief my explanation, here's the desired result

headquarter    sales      monthyear
   1           4500        202010
   1           5000        202011

So here 30th, 31st of Oct and 1st, 2nd of Nov falls in my condition and their values are summed to oct month. But 3rd, 4th of Nov values are summed to Nov month. Likewise it happens with every month. If I run the sql query, as today is 1st Nov (IST), today's value should be added to previous month.

I'm looking for some help in making the SQL query here.

Upvotes: 0

Views: 53

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1270081

MySQL offers the year_month specifier for extract(). I assume you want everything from the 2nd of one month to the first of the next month. If so, subtract one day:

select headquarter,
       extract(year_month from date - interval 1 day) as yyyymm,
       sum(sales)
from sample
group by headquarter, yyyymm;

Upvotes: 1

Akina
Akina

Reputation: 42661

GROUP BY DATE_FORMAT(`date` - INTERVAL 1 DAY, '%y%m')

Upvotes: 2

Related Questions