Get last 3 months data, summing totals for each month and show each month's data separately

Black Sulfur
Black Sulfur used Ask the Experts™
on
I want to get the past 3 months sales data and display it something like:

August 2018 - $.....
July 2018 - $....
June 2018 - $.....

I have tried something like this:

        $this->db->query("SELECT SUM(`order_total`) as `totalSales`
        FROM `order_summary`
        WHERE `order_date` >= LAST_DAY(NOW()) + INTERVAL 1 DAY - INTERVAL 3 MONTH
        ");
        $results = $this->db->resultSet();
        return $results;

Open in new window


And I tried to put `order_total` in a foreach loop but it just spits out one figure. I am guessing there is something wrong with the SQL query?
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Jan LouwerensSoftware Engineer

Commented:
You want something like:

SELECT TRUNC(`order_date`, 'MM'), SUM(`order_total`) AS `totalSales` FROM `order_summary` WHERE
   `order_date` >= LAST_DAY(NOW()) + INTERVAL 1 DAY - INTERVAL 3 MONTH
GROUP BY TRUNC(`order_date`, 'MM')
ORDER BY TRUNC(`order_date`, 'MM') DESC

Open in new window

Author

Commented:
Thanks Jan,

I changed TRUNC to TRUNCATE as I don't think mysql works with TRUNC. That gave me a LOT of records and not just past 3 months. It should just output 3 rows.

Author

Commented:
So, I got this working. Well, sort of.

SELECT DATE_FORMAT(order_date, '%b') AS month, SUM(order_total) as totalSales
FROM order_summary
WHERE order_date <= NOW()
and order_date >= Date_add(Now(),interval - 3 month)
AND `order_status` = 'Approved'
GROUP BY DATE_FORMAT(order_date, '%m-%Y')
ORDER BY `order_date` DESC

Open in new window


It doesn't show months with no sales data. It should still show the month but have a 0 as the total.
Ensure you’re charging the right price for your IT

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

Jan LouwerensSoftware Engineer

Commented:
You won't be able to return data in the query that doesn't exist in the table, so you'll need to create that data yourself.
Something kind of like:

SELECT month, SUM(totalSales) as totalSales FROM
(
   SELECT DATE_FORMAT(order_date, '%b') AS month, SUM(order_total) as totalSales
   FROM order_summary
   WHERE order_date <= NOW()
   and order_date >= Date_add(Now(),interval - 3 month)
   AND `order_status` = 'Approved'
   GROUP BY DATE_FORMAT(order_date, '%m-%Y')

   UNION ALL
   SELECT  Date_add(Now(),interval - 2 month) AS month, 0 as totalSales
   UNION ALL
   SELECT  Date_add(Now(),interval - 1 month) AS month, 0 as totalSales
   UNION ALL
   SELECT  Date_add(Now(),interval) AS month, 0 as totalSales
)
GROUP BY month
ORDER BY month DESC

Open in new window

Author

Commented:
Thanks, Jan.

I am getting an error when running that code:

Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ') AS month, 0 as totalSales
        )
        GROUP BY month
        ORDER BY mo'
Raja Jegan RSQL Server DBA & Architect, EE Solution Guide
Awarded 2009
Distinguished Expert 2018

Commented:
There was a missing alias name for your derived table, add an alias name for this derived table to get it fixed out..
) test
GROUP BY month
ORDER BY month DESC

Open in new window

Author

Commented:
Thanks Raja, it's still not happy.

Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ') AS month, 0 as totalSales
            ) test
            GROUP BY month
      '

SELECT month, SUM(totalSales) as totalSales FROM
(
   SELECT DATE_FORMAT(order_date, '%b') AS month, SUM(order_total) as totalSales
   FROM order_summary
   WHERE order_date <= NOW()
   and order_date >= Date_add(Now(),interval - 3 month)
   AND `order_status` = 'Approved'
   GROUP BY DATE_FORMAT(order_date, '%m-%Y')

   UNION ALL
   SELECT  Date_add(Now(),interval - 2 month) AS month, 0 as totalSales
   UNION ALL
   SELECT  Date_add(Now(),interval - 1 month) AS month, 0 as totalSales
   UNION ALL
   SELECT  Date_add(Now(),interval) AS month, 0 as totalSales
) test
GROUP BY month
ORDER BY month DESC
");

Open in new window

Raja Jegan RSQL Server DBA & Architect, EE Solution Guide
Awarded 2009
Distinguished Expert 2018

Commented:
oops, my mistake, in MySQL you need to use AS test instead of just alias name..
kindly try once..

Author

Commented:
Hmm. Still getting an error:

Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ') AS month, 0 as totalSales
            ) as test
            GROUP BY month
   '

SELECT month, SUM(totalSales) as totalSales FROM
(
   SELECT DATE_FORMAT(order_date, '%b') AS month, SUM(order_total) as totalSales
   FROM order_summary
   WHERE order_date <= NOW()
   and order_date >= Date_add(Now(),interval - 3 month)
   AND `order_status` = 'Approved'
   GROUP BY DATE_FORMAT(order_date, '%m-%Y')

   UNION ALL
   SELECT  Date_add(Now(),interval - 2 month) AS month, 0 as totalSales
   UNION ALL
   SELECT  Date_add(Now(),interval - 1 month) AS month, 0 as totalSales
   UNION ALL
   SELECT  Date_add(Now(),interval) AS month, 0 as totalSales
) as test
GROUP BY month
ORDER BY month DESC

Open in new window

Raja Jegan RSQL Server DBA & Architect, EE Solution Guide
Awarded 2009
Distinguished Expert 2018

Commented:
just noticed, why there are no inputs or interval values specified for this statement..
SELECT  Date_add(Now(),interval) AS month, 0 as totalSales

Open in new window

This could be simply
SELECT  Now() AS month, 0 as totalSales

Open in new window

Author

Commented:
Ah, thanks Raja, no more errors. But the output isn't what I expected.

In my original code I had this:

SELECT DATE_FORMAT(order_date, '%b') AS month, SUM(order_total) as totalSales
FROM order_summary
WHERE order_date <= NOW()
and order_date >= Date_add(Now(),interval - 3 month)
AND `order_status` = 'Approved'
GROUP BY DATE_FORMAT(order_date, '%m-%Y')
ORDER BY `order_date` DESC

Open in new window


Which I outputted using a foreach loop and got this result:

Sep - 100.00
Aug - 75.00

July did not display because there is no data but I would have liked it to show the month and 0 ie:

July - 0

After running the new code:

            SELECT month, SUM(totalSales) as totalSales FROM
            (
               SELECT DATE_FORMAT(order_date, '%b') AS month, SUM(order_total) as totalSales
               FROM order_summary
               WHERE order_date <= NOW()
               and order_date >= Date_add(Now(),interval - 3 month)
               AND `order_status` = 'Approved'
               GROUP BY DATE_FORMAT(order_date, '%m-%Y')

               UNION ALL
               SELECT  Date_add(Now(),interval - 2 month) AS month, 0 as totalSales
               UNION ALL
               SELECT  Date_add(Now(),interval - 1 month) AS month, 0 as totalSales
               UNION ALL
               SELECT  Now() AS month, 0 as totalSales
            ) as test
            GROUP BY month
            ORDER BY month DESC

Open in new window


My result is:

Sep - 100.00
Aug - 75.00
2018-09-12 09:19:05 - 0.00
2018-08-12 09:19:05 - 0.00
2018-07-12 09:19:05 - 0.00

Seems like
0 as totalSales

Open in new window

is perhaps setting totalSales to 0 regardless if there are figures for that month or not?
Raja Jegan RSQL Server DBA & Architect, EE Solution Guide
Awarded 2009
Distinguished Expert 2018

Commented:
Kindly try this one, you have received those date values since you haven't applied Date_format function for this code.. Since the totalsalees is hardcoded as 0, you are getting it as 0. If you wish to display some other values, then you might need to use the appropriate code in this piece of script.
SELECT  Date_add(Now(),interval - 2 month) AS month, 0 as totalSales
               UNION ALL
               SELECT  Date_add(Now(),interval - 1 month) AS month, 0 as totalSales
               UNION ALL
               SELECT  Now() AS month, 0 as totalSales

Open in new window

SELECT month, SUM(totalSales) as totalSales FROM
(
   SELECT DATE_FORMAT(order_date, '%b') AS month, SUM(order_total) as totalSales
   FROM order_summary
   WHERE order_date <= NOW()
   and order_date >= Date_add(Now(),interval - 3 month)
   AND `order_status` = 'Approved'
   GROUP BY DATE_FORMAT(order_date, '%m-%Y')

   UNION ALL
   SELECT  DATE_FORMAT(Date_add(Now(),interval - 2 month), '%b') AS month, 0 as totalSales
   UNION ALL
   SELECT DATE_FORMAT(Date_add(Now(),interval - 1 month), '%b') AS month, 0 as totalSales
   UNION ALL
   SELECT  DATE_FORMAT(Now(), '%b') AS month, 0 as totalSales
) as test
GROUP BY month
ORDER BY month DESC
");

Open in new window

Topic Advisor, Page Editor
Distinguished Expert 2018
Commented:
need all periods (months) in same format
            SELECT month, SUM(totalSales) as totalSales FROM
            (
               SELECT DATE_FORMAT(order_date, '%b') AS month, SUM(order_total) as totalSales
               FROM order_summary
               WHERE order_date <= NOW()
               and order_date >= Date_add(Now(),interval - 3 month)
               AND `order_status` = 'Approved'

               GROUP BY DATE_FORMAT(order_date, '%m-%Y')

               UNION ALL
               SELECT  DATE_FORMAT( (Date_add(Now(),interval - 2 month) ),'%b') AS month, 0 as totalSales
               UNION ALL
               SELECT  DATE_FORMAT( (Date_add(Now(),interval - 1 month) ),'%b')  AS month, 0 as totalSales
               UNION ALL
               SELECT  DATE_FORMAT(Now() ,'%b')  AS month, 0 as totalSales
            ) as test
            GROUP BY month
            ORDER BY month DESC

Open in new window

And probably dont need the inside group by either - given the group by in the outer query.

so, could be more easily written as
            SELECT DATE_FORMAT(order_date, '%b') as month, SUM(order_total) as totalSales FROM
            (
               SELECT order_date, order_total
               FROM order_summary
               WHERE order_date <= NOW()
               and order_date >= Date_add(Now(),interval - 3 month)
               AND `order_status` = 'Approved'
               
               UNION ALL
               SELECT  Date_add(Now(),interval - 2 month)  AS order_date, 0 as order_total
               UNION ALL
               SELECT  Date_add(Now(),interval - 1 month)   AS order_date, 0 as order_total
               UNION ALL
               SELECT  Now() AS AS order_date, 0 as order_total
            ) as test
            GROUP BY DATE_FORMAT(order_date, '%m-%Y')
            ORDER BY DATE_FORMAT(order_date, '%m-%Y') desc

Open in new window

Author

Commented:
Mark, if you are referring to this line:

GROUP BY DATE_FORMAT(order_date, '%m-%Y')

Open in new window


Removing that returns incorrect values and seems to sum all the months together instead of individual months.

There is only one issue left that I can see. Even though we are grouping by month, the month order is incorrect. This happens when using both Raj/Mark's code.

It is showing:

Sep - 100.00
Jul - 0.00
Aug - 75.00

instead of

Sept - 100.00
Aug - 75.00
Jul - 0.00
Mark WillsTopic Advisor, Page Editor
Distinguished Expert 2018

Commented:
The months will be grouped by the outer query anyway.... So, let it do the work of aggregating. Not really saving anything by grouping inside the subquery as well as outside the subquery.

Need to ORDER BY numeric month - not alphabetic month - hence my second example.

Did you try it or dismiss because of removing the internal group by ?

Author

Commented:
Sorry, seems like I replied after you updated your answer.

After running your second example code I get an error:

   
   SELECT DATE_FORMAT(order_date, '%b') as month, SUM(order_total) as totalSales FROM
            (
               SELECT order_date, order_total
               FROM order_summary
               WHERE order_date <= NOW()
               and order_date >= Date_add(Now(),interval - 3 month)
               AND `order_status` = 'Approved'
               
               UNION ALL
               SELECT  Date_add(Now(),interval - 2 month)  AS order_date, 0 as order_total
               UNION ALL
               SELECT  Date_add(Now(),interval - 1 month)   AS order_date, 0 as order_total
               UNION ALL
               SELECT  Now() AS AS order_date, 0 as order_total
            ) as test
            GROUP BY DATE_FORMAT(order_date, '%m-%Y')
            ORDER BY DATE_FORMAT(order_date, '%m-%Y') desc

Open in new window

Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'AS order_date, 0 as order_total
            ) as test
            GROUP BY DATE_'

Author

Commented:
Never mind, found the error. You had two AS's together ie: AS AS

  SELECT  Now() AS AS order_date, 0 as order_total

Open in new window


But after fixing that it seems to work great!
Mark WillsTopic Advisor, Page Editor
Distinguished Expert 2018

Commented:
Two AS's  ie :  SELECT  Now() AS AS order_date, 0 as order_total
try
   SELECT DATE_FORMAT(order_date, '%b') as month, SUM(order_total) as totalSales FROM
            (
               SELECT order_date, order_total
               FROM order_summary
               WHERE order_date <= NOW()
               and order_date >= Date_add(Now(),interval - 3 month)
               AND `order_status` = 'Approved'
               
               UNION ALL
               SELECT  Date_add(Now(),interval - 2 month)  AS order_date, 0 as order_total
               UNION ALL
               SELECT  Date_add(Now(),interval - 1 month)   AS order_date, 0 as order_total
               UNION ALL
               SELECT  Now() AS order_date, 0 as order_total
            ) as test
            GROUP BY DATE_FORMAT(order_date, '%m-%Y')
            ORDER BY DATE_FORMAT(order_date, '%m-%Y') desc

Open in new window

And should probably be sorting by %Y-%m in case you cross over year boundaries
Mark WillsTopic Advisor, Page Editor
Distinguished Expert 2018

Commented:
Well done, you found the error, and now have a working query :)

Happy to have been able to help.

Author

Commented:
Thanks to all for helping, it is much appreciated.
Raja Jegan RSQL Server DBA & Architect, EE Solution Guide
Awarded 2009
Distinguished Expert 2018

Commented:
Black Sulfur,

Small suggestion, your question was assisted first by Jan Louwerens and he helped frame your query but with some syntax issues.
I've helped resolve those syntax issues and brought the results into expected format..
Mark Wills have helped modify the query slightly to make it more simpler..
So, to be fair to appreciate the efforts of all, going forward please consider a fair split up of points..

Author

Commented:
Hi Raja,

Experts exchange has changed the way points are allocated. It used to really easy where you could actually see how many points you were awarding . Now you have no clue how many points each person gets because it doesn't show you anywhere. I personally don't like the new way it works and it seems you don't either. Perhaps if enough people take it up with EE then they will change it back.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial