4 replies to this topic

[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]

NodsDorf, on 03 May 2012 - 04:53 PM, said:

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]

bruyndoncx, on 03 May 2012 - 10:13 PM, said:

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.