asked on

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?

Jan Louwerens

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

Crazy Horse

ASKER

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.

Crazy Horse

ASKER

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.

Jan Louwerens

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

Crazy Horse

ASKER

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 R

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

Crazy Horse

ASKER

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 R

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

Crazy Horse

ASKER

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 R

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

Crazy Horse

ASKER

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 R

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

ASKER CERTIFIED SOLUTION

Mark Wills

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Crazy Horse

ASKER

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 Wills

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 ?

Crazy Horse

ASKER

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_'

Crazy Horse

ASKER

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 Wills

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 Wills

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

Happy to have been able to help.

Crazy Horse

ASKER

Thanks to all for helping, it is much appreciated.

Raja Jegan R

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..

Crazy Horse

ASKER

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.