CODEWITHSUNDEEP
mysqldatabase
Mary Jhealyn Villena
Mary Jhealyn Villena

Reputation: 101

MYSQL Select weekly and monthly from database

Hi I need help for my problem.

I want to produce monthly and weekly reports.

First of all I already created a database named calendar_table wherein it contains all the date form 2010 to 2040

To visualize the problem more :

SELECT DATE_FORMAT(DATE(calendar_table.c_date),'%d %b %Y') AS DATE, IFNULL(SUM(buyers_table.sales_total),0) AS total_sales FROM buyers_table RIGHT JOIN calendar_table ON (DATE(buyers_table.buyer_date) = calendar_table.c_date) WHERE (calendar_table.c_date BETWEEN DATE_SUB(NOW(), INTERVAL 1 WEEK) AND NOW()) GROUP BY calendar_table.c_date

The current output is:

DATE            total_sales
23 Nov 2016     0
24 Nov 2016     0
25 Nov 2016     0
26 Nov 2016     4500
27 Nov 2016     5800
28 Nov 2016     0
29 Nov 2016     3500

So it producing a daily output of a week instead of weekly .

My desire output should be like this:

DATE            total_sales
17 Oct 2016     0
24 Oct 2016     0
31 Oct 2016     0
07 Nov 2016     4500
14 Nov 2016     5800
21 Nov 2016     0
28 Nov 2016     3500

Of course the logic here is the same with monthly

Hope you help me I'm a little bit confuse :)

Thanks

Upvotes: 1

Views: 56

Answers (1)

ScaisEdge
ScaisEdge

Reputation: 133360

If week number is enough you can use week

You have asimmetric between select and group by (DATE_FORMAT(DATE(calendar_table.c_date),'%d %b %Y') is a day .. you more tha a result in a ween) try 

  SELECT date_add('2016-01-01', INTERVAL  week('calendar_table.c_date')-1 WEEK) AS DATE, 
  IFNULL(SUM(buyers_table.sales_total),0) AS total_sales 
  FROM buyers_table RIGHT JOIN calendar_table ON (DATE(buyers_table.buyer_date) = calendar_table.c_date) 
  WHERE (calendar_table.c_date BETWEEN DATE_SUB(NOW(), INTERVAL 7 WEEK) AND NOW()) 
  GROUP BY week('calendar_table.c_date')-1

and month for month 

  SELECT date_add('2016-01-01', INTERVAL  month('calendar_table.c_date')-1 month) AS DATE, 
  IFNULL(SUM(buyers_table.sales_total),0) AS total_sales 
  FROM buyers_table RIGHT JOIN calendar_table ON (DATE(buyers_table.buyer_date) = calendar_table.c_date) 
  WHERE (calendar_table.c_date BETWEEN DATE_SUB(NOW(), INTERVAL 7 WEEK) AND NOW()) 
  GROUP BY month('calendar_table.c_date')-1

Upvotes: 2

Related Questions