Jump to content

Mysql query help - error somewehre - cannot find it


Go to solution Solved by bambinou1980,

Recommended Posts

Hello,

 

I have this query which is returning a blank page at the moment:

 

$sql = "SELECT r.resellers_id,
               r.reseller_name,
               r.reseller_surname,
               r.reseller_email,
               r.reseller_phone,
               r.reseller_commission,
               r.reseller_vat_number,
               o.orders_id,
               o.reseller_earnings,
               o.order_status,
               o.has_reseller_been_paid,
               SUM(o.reseller_earnings) as total
               
               
               FROM orders AS o
               LEFT JOIN resellers as r
               ON o.orders_resellers_id = r.resellers_id
               WHERE o.due_date BETWEEN '2014-08-14' AND NOW()
               AND o.order_status LIKE '%Order Completed%'

               GROUP BY r.resellers_id
               ORDER BY total DESC";

 

Based on the below tables, could you please let me know if you see an aerror anywhere?

 

All I am trying to do is have all the resellers ids group together and show how much money we owe them each month.

For this I need to put the "UNPAID" resellers from the "o.has_reseller_been_paid"  at the top of the ladder with the sum of all their earnings from the o.reseller_earnings row.

 

ogjj95.jpg

 

 

Thank you!

Edited by Ch0cu3r

I'm confused. You started this topic saying that you have a blank page. Now in your last post you state that "some entries ..are empty". Big, big difference.

 

If you want to see the Resellers that have had sales in the last period then yes - use that table as your left side. Or do a right join. Currently you are selecting orders for the period and that should be working I believe. When you say 'blank' do you mean "missing reseller data"? Any blank reseller data would indicate a bad/missing id in that case.

Yes my apology in between I did change the qeury which produced some results.

 

So It is nearly there, the problem is that the reseller_earnings are not suming up, it is weird because it should work without problem, I am not seeing what is wrong here.

$sql = "SELECT r.resellers_id,
               r.reseller_name,
               r.reseller_surname,
               r.reseller_email,
               r.reseller_phone,
               r.reseller_commission,
               r.reseller_vat_number,
               o.orders_id,
               o.reseller_earnings,
               o.order_status,
               o.has_reseller_been_paid,
               SUM(o.reseller_earnings) as total
      FROM orders AS o
      LEFT JOIN resellers as r
      ON r.resellers_id = o.orders_resellers_id
      WHERE o.due_date BETWEEN '2014-08-14' AND '2016-08-14'
      GROUP BY r.resellers_id
               ORDER BY total DESC";​

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.