Query for most ordered products

[Former Member]

Hi , we were working on a query to find the most ordered products in descending order but however query is not working and giving missing '}' for '{' error. Could someone please suggest solutions. Here is the query:

select {p1:code} from {Myproduct as p1} join {SELECT {P.CODE} as p1code,COUNT({P.code}) as tcount FROM { order AS O JOIN orderentry AS OE ON {OE.order} = {O.pk} JOIN Myproduct AS P ON {OE.product} = {P.pk} }
GROUP BY {P.CODE}} as p2 on {p2:p1code}={p1:code} order by {p2.tcount} desc

[Former Member]

Hi Smriti,

Use below query if your requirement is to know the products which was ordered by most users.

 select {prod.code}, COUNT({e.quantity}) as prodcount from {MyProduct as prod},{OrderEntry as e}
  where {e.product} = {prod.pk} GROUP BY {prod.CODE}
  order by prodcount desc

If your requirement is to know a product which has been sold largely, you can use the below flexible query.

 select {prod.code}, SUM({e.quantity}) as prodcount from {MyProduct as prod},{OrderEntry as e}
 where {e.product} = {prod.pk} GROUP BY {prod.CODE}
 order by prodcount desc

Please let me know if you face any issues.

Thanks

[andyfletcher]

I don't think Flexible Search supports joins to subqueries. However if you can use a subquery in a WHERE or EXISTS clause when you need to wrap with ({{ }})

e.g.

 select {code} from {product} where {pk} in ({{select {product} from {orderentry}}})

but to just count top sold products don't you want something like this?

 select {p.code}, sum({oe.quantity})
 from {product as p join orderentry as oe on {oe.product}={p.pk}}
 group by {p.code}
 order by sum({oe.quantity}) desc

(this counts the actual quanity in the order rather than just counting the order entries themselves, e.g. in your query an orderentry with quantity 10 would only register as 1 sold)