Can somebody check this SQL drawing a blank...

[NodsDorf]

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.

[♥bruyndoncx]

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

[NodsDorf]

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

[♥bruyndoncx]

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.

[NodsDorf]

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.