Jump to content

Archived

This topic is now archived and is closed to further replies.

NodsDorf

Can somebody check this SQL drawing a blank...

Recommended Posts

SELECT p.products_id,p.products_model,p.products_ordered, count(op.orders_products_id) AS Sales
from products as p
INNER JOIN orders_products as op ON p.products_id=op.orders_products_id
where p.products_ordered >0 and p.products_model LIKE 'B%' ORDER BY p.products_ordered DESC

 

Just trying to see how many orders for these products have been placed. Not the total sold just unique orders...

products_id products_model products_ordered Sales NULL NULL NULL 0

 

Statement executes but no results which can't be correct.

 

I'm sure I'm missing something basic..

 

Any help is appreciated.

Share this post


Link to post
Share on other sites

last line misses group by

 

where p.products_ordered >0 and p.products_model LIKE 'B%'

group by p.products_id, p.products_model, p.products_ordered

ORDER BY p.products_ordered DESC


KEEP CALM AND CARRY ON

I do not use the responsive bootstrap version since i coded my responsive version earlier, but i have bought every 28d of code package to support burts effort and keep this forum alive (albeit more like on life support).

So if you are still here ? What are you waiting for ?!

 

Find the most frequent unique errors to fix:

grep "PHP" php_error_log.txt | sed "s/^.* PHP/PHP/g" |grep "line" |sort | uniq -c | sort -r > counterrors.txt

Share this post


Link to post
Share on other sites

Thank you Carine

 

I also missed that orders_products_id is the primary key fixed that and this seems to work.

 

SELECT p.products_id, p.products_model, p.products_ordered, count( op.products_id ) AS Sales
FROM products AS p
INNER JOIN orders_products AS op ON p.products_id = op.products_id
WHERE p.products_ordered >0
AND p.products_model LIKE 'B%'
GROUP BY p.products_id, p.products_model, p.products_ordered
ORDER BY `Sales` ASC

 

 

Thanks

Share this post


Link to post
Share on other sites

I also missed that orders_products_id is the primary key fixed that and this seems to work.

I don't see what the primary key has to do with this ? Anyhow, good it works ...

 

on a side note, if you need/want to run some reports regularly, the reportico opensource report generator written in php is fairly handy to have.

Maybe not the nicest, but it does the job quite well. I use it to report on anomalities in the data and generate the admin edit links in the report.


KEEP CALM AND CARRY ON

I do not use the responsive bootstrap version since i coded my responsive version earlier, but i have bought every 28d of code package to support burts effort and keep this forum alive (albeit more like on life support).

So if you are still here ? What are you waiting for ?!

 

Find the most frequent unique errors to fix:

grep "PHP" php_error_log.txt | sed "s/^.* PHP/PHP/g" |grep "line" |sort | uniq -c | sort -r > counterrors.txt

Share this post


Link to post
Share on other sites
I don't see what the primary key has to do with this ? Anyhow, good it works ... on a side note, if you need/want to run some reports regularly, the reportico opensource report generator written in php is fairly handy to have. Maybe not the nicest, but it does the job quite well. I use it to report on anomalities in the data and generate the admin edit links in the report.

 

Opps sorry didn't see your follow up @@bruyndoncx

 

In the case of the primary key, I was mentioning it because I was trying to find unique orders based on order ID and my first query was using the primary key which isn't the same as the order id. (

(op.orders_products_id vs op.products_id)

 

And thank you for the suggestion on reportico, I have taken a look at it and it seems very handy.

Share this post


Link to post
Share on other sites

×