Still some problems

[Erwin007]

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";

[Barand]

Your query evidently isn't processing the records you think it should. Check.

[Erwin007]

12 minutes ago, Barand said:

Your query evidently isn't processing the records you think it should. Check.

Yes that's my understanding too.
Maybe the reason for my post....

[gizmola]

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)

 

[Erwin007]

Thanks Gizmola, that did the trick.

[Barand]

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.

[Erwin007]

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 July 16, 2023 by Erwin007

[Erwin007]

Let me try somewhere else for some help.

[Barand]

Your post yesterday said you had given up - feel free.

[Barand]

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

 

[Erwin007]

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.