Jump to content

Still some problems


Erwin007
Go to solution Solved by Barand,

Recommended Posts

Hi,

 

Things worked out fine but I still have this (small) problem:
The totals are not correct:
Barcelo Gran Anejo(13) should be 12 (amount) and 156 (total) 
Brugal Leyenda(40) should be 7 (amount) and 280 (total)
Somewhere in the query I am making a mistake, I hope someone can find it.
Thanks.
 

$query = "

SELECT products.product_name, sum(salesdetails.salesdetails_amount) AS amount, sum(salesdetails.salesdetails_pricedollars) AS total

FROM `sales`

JOIN salesdetails ON salesdetails.salesdetails_salesticketnr = sales.sales_ticketnr

JOIN products ON (products.product_id = 55 OR products.product_id = 51)

WHERE salesdetails.salesdetails_productid = 55 OR salesdetails.salesdetails_productid = 51 AND sales.sales_date BETWEEN ? AND ?

GROUP BY products.product_name";

problem.thumb.jpg.80ae070ed763cdf45e020fbbe8fabdcb.jpg

Link to comment
Share on other sites

Not that this explains the problem, but if you want a grouping for these 2 products, then group by product.product_id, not product_name.  

Also, no reason to do this:

JOIN products ON (products.product_id = 55 OR products.product_id = 51)

You should be joining from the salesdetails row to product.

JOIN products ON (products.product_id = salesdetails.salesdetails_product_id)

 

Link to comment
Share on other sites

If you remove the SELECT and GROUP BY clauses from your original query and just SELECT salesdetails.*, giving something like this

SELECT salesdetails.*
FROM `sales`
JOIN salesdetails ON salesdetails.salesdetails_salesticketnr = sales.sales_ticketnr
JOIN products ON products.product_id IN (51,55)
WHERE salesdetails.salesdetails_productid IN (51,55) 
    AND sales.sales_date BETWEEN ? AND ?

... it will list the actual details records that are being processed in the query - some probably more than you expected because of the wrong join.

Link to comment
Share on other sites

Unfortunately it doesn't work.
After trying for days it's time to give up I guess.
I am so close with this SQL:
 

SELECT products.product_name,sum(salesdetails.salesdetails_amount) AS amount, sum(salesdetails.salesdetails_pricedollars) AS total
FROM `sales`
JOIN salesdetails ON salesdetails.salesdetails_salesticketnr = sales.sales_ticketnr
JOIN products ON products.product_id = salesdetails.salesdetails_productid
WHERE (sales.sales_date BETWEEN ? AND ?)
GROUP BY products.product_name";

 But instead of all the products I only need this SQL for productid's 50 till 56.
The code after the SQL is this:

$result = mysqli_prepare($con,$query);
$result->bind_param('ss', $from, $to);
$result->execute();
$result->bind_result($product_name, $amount, $total);

Maybe there is something incorrect.
 

Edited by Erwin007
Link to comment
Share on other sites

  • Solution
On 7/16/2023 at 3:19 PM, Erwin007 said:

But instead of all the products I only need this SQL for productid's 50 till 56.

Then include that condition in your WHERE clause

WHERE (sales.sales_date BETWEEN ? AND ?) AND products.product_id BETWEEN 50 AND 56

 

Link to comment
Share on other sites

14 minutes ago, Barand said:

Then include that condition in your WHERE clause

WHERE (sales.sales_date BETWEEN ? AND ?) AND products.product_id BETWEEN 50 AND 56

 

I am stunned.....it works.....🙂👍
And so simple...when you know it...
Appreciate your time, thanks.

Link to comment
Share on other sites

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.