user user - 2 months ago 10
MySQL Question

MySQL : weekly and monthly SUM

The below given is my TOUR table:

tour_id | tour_date | amount
------------------------------
ABC | 2016-03-07 | 100
ABC | 2016-03-14 | 200
DEF | 2016-03-07 | 300
DEF | 2016-03-14 | 100
GHI | 2016-03-07 | 30


I want to get the following:


  • sum total per week

  • sum total per month.



This final data must look like this:

tour_id| weekly sum. | monthly sum.
--------------------------------
ABC | 150 | 300
DEF | 200 | 500
GHI | 30 | 30

Answer Source
    SELECT 
    tour_id, WEEKLY, MONTHLY
FROM
    TOUR T1
        INNER JOIN
    (SELECT 
        tour_id, SUM(amount) WEEKLY
    FROM
        TOUR
    GROUP BY tour_id , WEEK(tour_date)) WEEK ON WEEK.tour_id = T1.tour_id
        INNER JOIN
    (SELECT 
        tour_id, SUM(amount) MONTHLY
    FROM
        TOUR
    GROUP BY tour_id , DATE_FORMAT(tour_date, '%m')) MONTH ON WEEK.tour_id = MONTH.tour_id

Try above query.

Hope this will help you.