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

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?
LVL 1
Black SulfurAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Jan LouwerensSoftware EngineerCommented:
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

0
Black SulfurAuthor 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.
0
Black SulfurAuthor 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.
0
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

Jan LouwerensSoftware EngineerCommented:
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

0
Black SulfurAuthor 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'
0
Raja Jegan RSQL Server DBA & Architect, EE Solution GuideCommented:
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

0
Black SulfurAuthor 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

0
Raja Jegan RSQL Server DBA & Architect, EE Solution GuideCommented:
oops, my mistake, in MySQL you need to use AS test instead of just alias name..
kindly try once..
0
Black SulfurAuthor 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

0
Raja Jegan RSQL Server DBA & Architect, EE Solution GuideCommented:
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

0
Black SulfurAuthor 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?
0
Raja Jegan RSQL Server DBA & Architect, EE Solution GuideCommented:
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

0
Mark WillsTopic AdvisorCommented:
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

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Black SulfurAuthor 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
0
Mark WillsTopic AdvisorCommented:
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 ?
0
Black SulfurAuthor 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_'
0
Black SulfurAuthor 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!
0
Mark WillsTopic AdvisorCommented:
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
0
Mark WillsTopic AdvisorCommented:
Well done, you found the error, and now have a working query :)

Happy to have been able to help.
0
Black SulfurAuthor Commented:
Thanks to all for helping, it is much appreciated.
0
Raja Jegan RSQL Server DBA & Architect, EE Solution GuideCommented:
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..
0
Black SulfurAuthor 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.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Query Syntax

From novice to tech pro — start learning today.