Combine 2 separate database queries in one foreach loop

Black Sulfur
Black Sulfur used Ask the Experts™
on
I have this query to check the last 3 months sales figures and another query to check the refund figures

       
           // sales
            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


           // refunds
        SELECT DATE_FORMAT(credit_date, '%b') as month, SUM(`credit_value`) as totalCredit FROM
            (
               SELECT credit_value, credit_date
               FROM credit
               WHERE credit_date <= NOW()
               and credit_date >= Date_add(Now(),interval - 3 month)
               AND `reason` = 'Refund'
               
               UNION ALL
               SELECT  Date_add(Now(),interval - 2 month)  AS credit_date, 0 as credit_value
               UNION ALL
               SELECT  Date_add(Now(),interval - 1 month)   AS credit_date, 0 as credit_value
               UNION ALL
               SELECT  Now() AS credit_date, 0 as credit_value
            ) as test
            GROUP BY DATE_FORMAT(credit_date, '%m-%Y')
            ORDER BY DATE_FORMAT(credit_date, '%m-%Y') desc
            ");

Open in new window


I want to show the data in a table like:

Month          Sales          Refunds

Sep                100                 0
Aug                100                20
Jul                   50                   5

I thought of trying to combine the queries into one but that seems very complex. And trying to nest the foreach loops creates 6 rows instead of 3. What would be the best way to achieve this?
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
ste5anSenior Developer

Commented:
To minimize the database roundtrip you can simply union both. Just add a discriminator (source) constant column.

Just a comment:

You're shifting too much work to the database level. You're seeking for a display format. Thus I would run both queries separately. Without the inner unions for the 0 rows, cause these 0 rows/cells can be easily created in your front-end.

Author

Commented:
Thanks ste5an,

I am trying to do what you suggested

SELECT DATE_FORMAT(order_date, '%b') as month, SUM(order_total) as totalSales, SUM(credit_value) as totalCredit 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 credit_date, credit_value
   FROM credit
   WHERE credit_date <= NOW()
   and credit_date >= Date_add(Now(),interval - 3 month)
   AND `reason` = 'Refund'
) as test
GROUP BY DATE_FORMAT(order_date, '%m-%Y')
ORDER BY DATE_FORMAT(order_date, '%m-%Y') DESC

Open in new window


But am getting an error:

Column not found: 1054 Unknown column 'credit_value' in 'field list' in

I could be wrong but I think the issue is coming from here:

SELECT DATE_FORMAT(order_date, '%b') as month, SUM(order_total) as totalSales, SUM(credit_value) as totalCredit FROM

Open in new window


I am also only selecting order_date but I also actually need credit_date
Hi Black,

The UNION ALL operator takes the rows in the lower sub-query and appends them to the rows in the upper sub-query.  The row names come form the upper sub-query so all of the data is there, but you have to reference it by the correct name.

A ste5an noted, a comment column that identifies the row's source is probably needed.  Since your filter has an order_status or reason identifier, that would do nicely.

Try something like this:

SELECT DATE_FORMAT(order_date, '%b') as month, 
  SUM(case when order_status='Approved' then order_total else 0 end) as totalSales, 
  SUM(case when order_Status='Refund' the order_total else 0 end) as totalCredit 
FROM
(
   SELECT order_status, 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 reason, credit_date, credit_value
   FROM credit
   WHERE credit_date <= NOW()
   and credit_date >= Date_add(Now(),interval - 3 month)
   AND `reason` = 'Refund'
) as test
GROUP BY DATE_FORMAT(order_date, '%m-%Y')
ORDER BY DATE_FORMAT(order_date, '%m-%Y') DESC

Open in new window


Good Luck,
Kent
Build an E-Commerce Site with Angular 5

Learn how to build an E-Commerce site with Angular 5, a JavaScript framework used by developers to build web, desktop, and mobile applications.

Author

Commented:
Thank you Kent, that seems to be working nicely. However, I am still missing the month with zero data. It should just show the month and a bunch of $0.00 values. ste5an pointed out that it would be easy to fix this in the frontend instead of trying to do it with the database as I was trying to do. But I am not really sure how that would be achieved. I could try wrap two sets of <tr> in if/else statements but that is maybe cumbersome?

Here is my foreach loop. Please note that the empty <tr> is where I plan to put more data.

<?php foreach($data['3months'] as $sales): ?>
<tr>
    <th scope="row">
        <?php echo $sales->month; ?>
    </th>
    <td>

    </td>
    <td>

    </td>
    <td>

    </td>
    <td>
         <?php echo $sales->totalCredit; ?>
    </td>
    <td>
         <?php echo $sales->totalSales; ?>
    </td>
</tr>
<?php endforeach; ?>

Open in new window

It appears to me that the report won't necessarily produce the results that you want.  By by selecting data generated withing the last 3 months, the starting point of the data is relative to the current date, not the first of the month.  The June data in the report, if run today, would include the data between June 12 and June 30.  Is that the intent, or do you want the entire month's data?

Author

Commented:
I want the sales data for each month from the first of the month until last day of the month for previous months but for current month should just be from 1st of the month until current date.
I see that you had a similar question yesterday.  Did you not get a satisfactory answer?

Author

Commented:
The question yesterday was just for the sales. Today I need sales and refunds.
I don't have a MySQL environment available to me right now so I can't debug this, but it should be pretty close...

WITH BaseDate (StartDate)
AS (
  SELECT Date_sub (Date_add (last_day (Now()), interval 1 day), interval 4 months) as StartDate
),
MonthRanges (StartDate)
AS (
  SELECT StartDate Month
  UNION ALL
  SELECT Date_add (StartDate, interval 1 month)
  UNION ALL
  SELECT Date_add (StartDate, interval 2 months)
  UNION ALL
  SELECT Date_add (StartDate, interval 3 months)
)
SELECT DATE_FORMAT (t0.StartDate, '%m-%Y') Month, t1.TotalSales, t1.totalCredit
FROM MonthRanges t0
LEFT JOIN
(
  SELECT DATE_SUB(last_day(order_date), interval 1 month) as month, 
  SUM(case when order_status='Approved' then order_total else 0 end) as totalSales, 
  SUM(case when order_Status='Refund' the order_total else 0 end) as totalCredit 
  FROM
  (
    SELECT order_status, order_date, order_total
    FROM order_summary
    WHERE order_date <= NOW()
      and order_date >= Date_sub(last_day(Now()) ,interval 4 months)
      AND `order_status` = 'Approved'
    UNION ALL
    SELECT reason, credit_date, credit_value
    FROM credit
    WHERE credit_date <= NOW()
      and credit_date >= Date_sub(last_day(Now()),interval 4 months)
      AND `reason` = 'Refund'
  ) t0      
  GROUP BY DATE_SUB(last_day(order_date), interval 1 month)
) as t1
ON t0.Month = t1.Month
  

Open in new window

Author

Commented:
Thanks, Kent. That gives me 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 'BaseDate (StartDate)
AS (
  SELECT Date_sub
It looks like 'month' cant be used as a column name without quoting it.  So we'll change it!  :)

WITH BaseDate (StartDate)
AS (
  SELECT Date_sub (Date_add (last_day (Now()), interval 1 day), interval 4 months) as StartDate
),
MonthRanges (MonthName)
AS (
  SELECT StartDate MonthName
  UNION ALL
  SELECT Date_add (StartDate, interval 1 month)
  UNION ALL
  SELECT Date_add (StartDate, interval 2 months)
  UNION ALL
  SELECT Date_add (StartDate, interval 3 months)
)
SELECT DATE_FORMAT (t0.StartDate, '%m-%Y') Month, t1.TotalSales, t1.totalCredit
FROM MonthRanges t0
LEFT JOIN
(
  SELECT DATE_SUB(last_day(order_date), interval 1 month) as MonthName, 
  SUM(case when order_status='Approved' then order_total else 0 end) as totalSales, 
  SUM(case when order_Status='Refund' the order_total else 0 end) as totalCredit 
  FROM
  (
    SELECT order_status, order_date, order_total
    FROM order_summary
    WHERE order_date <= NOW()
      and order_date >= Date_sub(last_day(Now()) ,interval 4 months)
      AND `order_status` = 'Approved'
    UNION ALL
    SELECT reason, credit_date, credit_value
    FROM credit
    WHERE credit_date <= NOW()
      and credit_date >= Date_sub(last_day(Now()),interval 4 months)
      AND `reason` = 'Refund'
  ) t0      
  GROUP BY DATE_SUB(last_day(order_date), interval 1 month)
) as t1
ON t0.MonthName = t1.MonthName
  

Open in new window

Author

Commented:
Thanks again Kent, still getting errors:

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 'BaseDate (StartDate)
AS (
  SELECT Date_sub (Date_add (last_day (Now()), interva
WITH BaseDate (StartDate)
AS (
  SELECT Date_sub (Date_add (last_day (Now()), interval 1 day), interval 4 months) as StartDate
),
MonthRanges (MonthName)
AS (
  SELECT StartDate MonthName FROM BaseDate
  UNION ALL
  SELECT Date_add (StartDate, interval 1 month) FROM BaseDate
  UNION ALL
  SELECT Date_add (StartDate, interval 2 months) FROM BaseDate
  UNION ALL
  SELECT Date_add (StartDate, interval 3 months) FROM BaseDate
)
SELECT DATE_FORMAT (t0.StartDate, '%m-%Y') Month, t1.TotalSales, t1.totalCredit
FROM MonthRanges t0
LEFT JOIN
(
  SELECT DATE_SUB(last_day(order_date), interval 1 month) as MonthName, 
  SUM(case when order_status='Approved' then order_total else 0 end) as totalSales, 
  SUM(case when order_Status='Refund' the order_total else 0 end) as totalCredit 
  FROM
  (
    SELECT order_status, order_date, order_total
    FROM order_summary
    WHERE order_date <= NOW()
      and order_date >= Date_sub(last_day(Now()) ,interval 4 months)
      AND `order_status` = 'Approved'
    UNION ALL
    SELECT reason, credit_date, credit_value
    FROM credit
    WHERE credit_date <= NOW()
      and credit_date >= Date_sub(last_day(Now()),interval 4 months)
      AND `reason` = 'Refund'
  ) t0      
  GROUP BY DATE_SUB(last_day(order_date), interval 1 month)
) as t1
ON t0.MonthName = t1.MonthName

Open in new window


This would be so much easier if I had MySQL available to me here...  Apologies....

Author

Commented:
Hi Kent, sorry for delayed response. Sorry for just posting errors but it is hard for me to troubleshoot as I am unfamiliar with some of the sql you are using like BaseDate and MonthRanges for example.

Uncaught PDOException: SQLSTATE[42000]: 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 'BaseDate (StartDate)
AS (
  SELECT Date_sub (Date_add (last_day (Now()), interva
Hi Black,

One of the issues with MySQL that I keep forgetting is it's intolerance for spaces between names and left parens.  FN (x) and FN(x) don't always parse equally.

But since we're spinning our wheels here, let's try this from another direction.

SELECT t1.ReportPeriod, 
  SUM(case when order_status='Approved' then order_total else 0 end) as totalSales, 
  SUM(case when order_Status='Refund' the order_total else 0 end) as totalCredit 
FROM
(
  SELECT DATE_FORMAT (Now(), '%m-%Y') ReportDate
  UNION ALL
  SELECT DATE_FORMAT (Date_sub (Now(), interval 1 month), '%m-%Y') 
  UNION ALL
  SELECT DATE_FORMAT (Date_sub (Now(), interval 2 month), '%m-%Y') 
  UNION ALL
  SELECT DATE_FORMAT (Date_sub (Now(), interval 3 month), '%m-%Y') 
) t0
LEFT JOIN
(
  SELECT Date_sub (order_date, '%m-%Y') ReportPeriod, order_status, order_date, order_total
  FROM order_summary
  WHERE order_date <= NOW()
    and order_date >= Date_sub(Date_add(last_day(Now()), interval 1 day), interval 4 months)
    AND `order_status` = 'Approved'
  UNION ALL
  SELECT Date_sub (order_date, '%m-%Y') ReportPeriod, reason, credit_date, credit_value
  FROM credit
  WHERE credit_date <= NOW()
    and credit_date >= Date_sub(Date_add(last_day(Now()),interval 1 day), interval 4 months)
    AND `reason` = 'Refund'
) t1
on t0.ReportPeriod = t1.ReportPeriod
GROUP BY t1.ReportPeriod

Open in new window

Author

Commented:
If this is getting too frustrating for you, I won't hold it against you if you abandon the question. But in case you want to go on,I fixed the missing 'n' in 'then' on line 3 of your code, but am 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 '), '%m-%Y')
  UNION ALL
  SELECT DATE_FORMAT (Date_sub (Now(), interval 2 month'
I don't give up nearly that easily....  :)

The parser is some of the issue as the string you posted doesn't exist in the query.  

SELECT t1.ReportPeriod, 
  SUM(case when order_status='Approved' then order_total else 0 end) as totalSales, 
  SUM(case when order_Status='Refund' then order_total else 0 end) as totalCredit 
FROM
(
  SELECT DATE_FORMAT(Now(), '%m-%Y') ReportDate
  UNION ALL
  SELECT DATE_FORMAT(Date_sub(Now(), interval 1 month), '%m-%Y') 
  UNION ALL
  SELECT DATE_FORMAT(Date_sub(Now(), interval 2 month), '%m-%Y') 
  UNION ALL
  SELECT DATE_FORMAT(Date_sub(Now(), interval 3 month), '%m-%Y') 
) t0
LEFT JOIN
(
  SELECT Date_sub(order_date, '%m-%Y') ReportPeriod, order_status, order_date, order_total
  FROM order_summary
  WHERE order_date <= NOW()
    and order_date >= Date_sub(Date_add(last_day(Now()), interval 1 day), interval 4 months)
    AND `order_status` = 'Approved'
  UNION ALL
  SELECT Date_sub(order_date, '%m-%Y') ReportPeriod, reason, credit_date, credit_value
  FROM credit
  WHERE credit_date <= NOW()
    and credit_date >= Date_sub(Date_add(last_day(Now()),interval 1 day), interval 4 months)
    AND `reason` = 'Refund'
) t1
on t0.ReportPeriod = t1.ReportPeriod
GROUP BY t1.ReportPeriod

Open in new window

Author

Commented:
That's the spirit! Another error for you :P

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 ''%m-%Y') ReportPeriod, order_status, order_date, order_total
  FROM order_summar'

So, this works for only orders:

            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


And this works for orders and refunds but doesn't show the months with zero:

SELECT DATE_FORMAT(order_date, '%b') as month, 
  SUM(case when order_status='Approved' then order_total else 0 end) as totalSales, 
  SUM(case when order_Status='Refund' then order_total else 0 end) as totalCredit 
FROM
(
   SELECT order_status, 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 reason, credit_date, credit_value
   FROM credit
   WHERE credit_date <= NOW()
   and credit_date >= Date_add(Now(),interval - 3 month)
   AND `reason` = 'Refund'
) as test
GROUP BY DATE_FORMAT(order_date, '%m-%Y')
ORDER BY DATE_FORMAT(order_date, '%m-%Y') DESC

Open in new window


They just need to be combined somehow...
Ahha!  Found a really glaring error select from the CREDIT table.

The two queries are fundamentally the same.  The new on just adds logic to create all of the periods and join the results to them.


SELECT t1.ReportPeriod, 
  SUM(case when order_status='Approved' then order_total else 0 end) as totalSales, 
  SUM(case when order_Status='Refund' then order_total else 0 end) as totalCredit 
FROM
(
  SELECT DATE_FORMAT(Now(), '%m-%Y') ReportDate
  UNION ALL
  SELECT DATE_FORMAT(Date_sub(Now(), interval 1 month), '%m-%Y') 
  UNION ALL
  SELECT DATE_FORMAT(Date_sub(Now(), interval 2 month), '%m-%Y') 
  UNION ALL
  SELECT DATE_FORMAT(Date_sub(Now(), interval 3 month), '%m-%Y') 
) t0
LEFT JOIN
(
  SELECT Date_sub(order_date, '%m-%Y') ReportPeriod, order_status, order_date, order_total
  FROM order_summary
  WHERE order_date <= NOW()
    and order_date >= Date_sub(Date_add(last_day(Now()), interval 1 day), interval 4 months)
    AND `order_status` = 'Approved'
  UNION ALL
  SELECT Date_sub(credit_date, '%m-%Y') ReportPeriod, reason, credit_date, credit_value
  FROM credit
  WHERE credit_date <= NOW()
    and credit_date >= Date_sub(Date_add(last_day(Now()),interval 1 day), interval 4 months)
    AND `reason` = 'Refund'
) t1
on t0.ReportPeriod = t1.ReportPeriod
GROUP BY t1.ReportPeriod

Open in new window

Author

Commented:
:)

Uncaught PDOException: SQLSTATE[42000]: 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 ''%m-%Y') ReportPeriod, order_status, order_date, order_total
  FROM order_summar

Why do you have interval 1 day in there?
The query calls last_day() on the current date (now) and adds 1 day to it (interval 1 day) to get the first day of the following month.  Back up 4 months (interval 4 days) and that's the first date that the query will report.

I've had a heck of a time getting MySQL to install on my new desktop.  It requires a version of .NET that is not necessarily compatible with other tools.  :(

This shouldn't be this tough, even without a test environment!


SELECT t0.ReportPeriod
  SUM(case when order_status='Approved' then order_total else 0 end) as totalSales, 
  SUM(case when order_Status='Refund' then order_total else 0 end) as totalCredit 
FROM
(
  SELECT DATE_FORMAT(Now(), '%m-%Y') ReportPeriod
  UNION ALL
  SELECT DATE_FORMAT(Date_sub(Now(), interval 1 month), '%m-%Y') 
  UNION ALL
  SELECT DATE_FORMAT(Date_sub(Now(), interval 2 month), '%m-%Y') 
  UNION ALL
  SELECT DATE_FORMAT(Date_sub(Now(), interval 3 month), '%m-%Y') 
) t0
LEFT JOIN
(
  SELECT Date_sub(order_date, '%m-%Y') ReportPeriod, order_status, order_date, order_total
  FROM order_summary
  WHERE order_date <= NOW()
    and order_date >= Date_sub(Date_add(last_day(Now()), interval 1 day), interval 4 months)
    AND `order_status` = 'Approved'
  UNION ALL
  SELECT Date_sub(credit_date, '%m-%Y') ReportPeriod, reason, credit_date, credit_value
  FROM credit
  WHERE credit_date <= NOW()
    and credit_date >= Date_sub(Date_add(last_day(Now()),interval 1 day), interval 4 months)
    AND `reason` = 'Refund'
) t1
ON t0.ReportPeriod = t1.ReportPeriod
GROUP BY t1.ReportPeriod

Open in new window

Author

Commented:
Sorry for all the trouble. I appreciate your persistence!

New 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 ''%m-%Y') ReportPeriod, order_status, order_date, order_total
  FROM order_summar'
Let's break the query down into smaller pieces and see what happens.  :)

First the upper query.

  SELECT DATE_FORMAT(Now(), '%m-%Y') ReportPeriod
  UNION ALL
  SELECT DATE_FORMAT(Date_sub(Now(), interval 1 month), '%m-%Y') 
  UNION ALL
  SELECT DATE_FORMAT(Date_sub(Now(), interval 2 month), '%m-%Y') 
  UNION ALL
  SELECT DATE_FORMAT(Date_sub(Now(), interval 3 month), '%m-%Y') 

Open in new window


Then the lower query:

  SELECT Date_sub(order_date, '%m-%Y') ReportPeriod, order_status, order_date, order_total
  FROM order_summary
  WHERE order_date <= NOW()
    and order_date >= Date_sub(Date_add(last_day(Now()), interval 1 day), interval 4 months)
    AND `order_status` = 'Approved'
  UNION ALL
  SELECT Date_sub(credit_date, '%m-%Y') ReportPeriod, reason, credit_date, credit_value
  FROM credit
  WHERE credit_date <= NOW()
    and credit_date >= Date_sub(Date_add(last_day(Now()),interval 1 day), interval 4 months)
    AND `reason` = 'Refund'

Open in new window

Author

Commented:
Hi Kent, sorry for the delayed response. Running the first block of code throws no errors. Running the second block however gives me:

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 ''%m-%Y') ReportPeriod, order_status, order_date, order_total
  FROM order_summar
Let's split up the offending SQL, too.  Try each of these.

  SELECT Date_sub(order_date, '%m-%Y') ReportPeriod, order_status, order_date, order_total
  FROM order_summary
  WHERE order_date <= NOW()
    and order_date >= Date_sub(Date_add(last_day(Now()), interval 1 day), interval 4 months)
    AND `order_status` = 'Approved'

Open in new window


  SELECT Date_sub(credit_date, '%m-%Y') ReportPeriod, reason, credit_date, credit_value
  FROM credit
  WHERE credit_date <= NOW()
    and credit_date >= Date_sub(Date_add(last_day(Now()),interval 1 day), interval 4 months)
    AND `reason` = 'Refund'

Open in new window


  SELECT Date_sub(order_date, '%m-%Y') AS ReportPeriod, order_status, order_date, order_total
  FROM order_summary
  WHERE order_date <= NOW()
    and order_date >= Date_sub(Date_add(last_day(Now()), interval 1 day), interval 4 months)
    AND `order_status` = 'Approved'

Open in new window

  SELECT Date_sub(credit_date, '%m-%Y') As ReportPeriod, reason, credit_date, credit_value
  FROM credit
  WHERE credit_date <= NOW()
    and credit_date >= Date_sub(Date_add(last_day(Now()),interval 1 day), interval 4 months)
    AND `reason` = 'Refund'

Open in new window

Author

Commented:
Hi there,

Okay, they all failed. Here is the error for the first one

PHP Fatal error:  Uncaught PDOException: SQLSTATE[42000]: 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 ''%m-%Y') ReportPeriod, order_status, order_date, order_total
  FROM order_summar' at line 1

and the last one:

PHP Fatal error:  Uncaught PDOException: SQLSTATE[42000]: 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 ''%m-%Y') As ReportPeriod, reason, credit_date, credit_value
  FROM credit
  WHER' at line 1
Thanks!  The fact that none of them worked told me a lot.  Basically, the call to Date_sub at the start of each query should have been a call to Date_format!  It just kind of disappeared into the shadows as I looked at the code until that clue tied it all together.  Another issue is that the interval in MYSQL date functions is singular.  'interval 4 months' is invalid, but 'interval 4 month' is accepted.

Try this:

SELECT t0.ReportPeriod,
  SUM(case when order_status='Approved' then order_total else 0 end) as totalSales, 
  SUM(case when order_Status='Refund' then order_total else 0 end) as totalCredit 
FROM
(
  SELECT DATE_FORMAT(Now(), '%m-%Y') ReportPeriod
  UNION ALL
  SELECT DATE_FORMAT(Date_sub(Now(), interval 1 month), '%m-%Y') 
  UNION ALL
  SELECT DATE_FORMAT(Date_sub(Now(), interval 2 month), '%m-%Y') 
  UNION ALL
  SELECT DATE_FORMAT(Date_sub(Now(), interval 3 month), '%m-%Y') 
  UNION ALL
  SELECT DATE_FORMAT(Date_sub(Now(), interval 4 month), '%m-%Y') 
) t0
LEFT JOIN
(
  SELECT Date_format(order_date, '%m-%Y') ReportPeriod, order_status, order_date, order_total
  FROM order_summary
  WHERE order_date <= NOW()
    and order_date >= Date_sub(Date_add(last_day(Now()), interval 1 day), interval 4 month)
    AND `order_status` = 'Approved'
  UNION ALL
  SELECT Date_format(credit_date, '%m-%Y') ReportPeriod, reason, credit_date, credit_value
  FROM credit
  WHERE credit_date <= NOW()
    and credit_date >= Date_sub(Date_add(last_day(Now()),interval 1 day), interval 4 month)
    AND `reason` = 'Refund'
) t1
ON t0.ReportPeriod = t1.ReportPeriod
GROUP BY t1.ReportPeriod
ORDER BY ReportPeriod

Open in new window

Hi Black,

Testing SQL through PHP can be slow and laborious.  I wasn't able to modify either of my environments to install MySQL, but I found something to help me test and thought that I'd pass it along.

  http://sqlfiddle.com/

It creates a local session unique to you that allows you to build and test your SQL.  The left window is the semi-static DDL (create table, insert data, etc.) and the right window is your SQL.  You might want to give it a try.  It looks like it could make your SQL development easier than writing it into your PHP.  One caution is that I have no idea who runs the site or what happens to your declarations so don't enter anything that is sensitive or identifying.

To test the SQL, I entered this as the DDL (left window):

CREATE TABLE credit (credit_date date, reason varchar(20), credit_value float);
CREATE TABLE order_summary (order_date date, order_status varchar(20), order_total float);

INSERT INTO credit (credit_date, reason, credit_value) 
values ('2018-07-07', 'Refund', 123.45), ('2018-08-08', 'Refund', 567.89);                                                               

INSERT INTO order_summary (order_date, order_status, order_total) 
values ('2018-07-12', 'Approved', 123.45), ('2018-09-01', 'Approved', 567.89);                                                               

Open in new window


And this into the right:

SELECT t0.ReportPeriod,
  SUM(case when order_status='Approved' then order_total else 0 end) as totalSales, 
  SUM(case when order_Status='Refund' then order_total else 0 end) as totalCredit 
FROM
(
  SELECT DATE_FORMAT(Now(), '%m-%Y') ReportPeriod
  UNION ALL
  SELECT DATE_FORMAT(Date_sub(Now(), interval 1 month), '%m-%Y') 
  UNION ALL
  SELECT DATE_FORMAT(Date_sub(Now(), interval 2 month), '%m-%Y') 
  UNION ALL
  SELECT DATE_FORMAT(Date_sub(Now(), interval 3 month), '%m-%Y') 
  UNION ALL
  SELECT DATE_FORMAT(Date_sub(Now(), interval 4 month), '%m-%Y') 
) t0
LEFT JOIN
(
  SELECT Date_format(order_date, '%m-%Y') ReportPeriod, order_status, order_date, order_total
  FROM order_summary
  WHERE order_date <= NOW()
    and order_date >= Date_sub(Date_add(last_day(Now()), interval 1 day), interval 4 month)
    AND `order_status` = 'Approved'
  UNION ALL
  SELECT Date_format(credit_date, '%m-%Y') ReportPeriod, reason, credit_date, credit_value
  FROM credit
  WHERE credit_date <= NOW()
    and credit_date >= Date_sub(Date_add(last_day(Now()),interval 1 day), interval 4 month)
    AND `reason` = 'Refund'
) t1
ON t0.ReportPeriod = t1.ReportPeriod
GROUP BY t1.ReportPeriod
ORDER BY ReportPeriod

Open in new window


Good Luck!
Kent

Author

Commented:
Hi Kent, apologies for the delayed response. I have been rather busy!  Will test this all out ASAP and get back to you. Thanks again.

Author

Commented:
Brilliant, that works! Thanks so much for all your effort and patience :)

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