SQL Query Top Seller Article by Category

English forums
1

Hello,

I’m new here in the forum, and i want firstly congrat the oxid project.

So, i’m trying to figure out how to make an SQL query to return the top seller article grouped by category name.

For now i have this SQL:

SELECT c.OXTITLE, a.OXARTNUM, SUM(oa.OXAMOUNT) as TotalAmount 
FROM oxorderarticles oa, oxorder o, oxarticles a, oxarticles aa, oxobject2category ac, oxcategories c
WHERE oa.OXARTID=a.OXID
AND oa.OXORDERID = o.OXID
AND aa.OXID = a.OXPARENTID
AND aa.OXID = ac.OXOBJECTID
AND ac.OXCATNID = c.OXID
AND (DATE(o.OXORDERDATE) BETWEEN '2012-08-01' AND '2012-08-10')
GROUP BY c.OXTITLE
ORDER BY TotalAmount DESC

I think i need to use a sub-query, because i’m returning the amount of all sells by category and not the top seller article by category.

Any advice?

Thanks in advance,
Luís

2

why dont you make use of the framework and oop mvs architecture?

3

Hello vanilla thunder,

Thanks for your feedback. It’s a valid option, but i need a very quick solution using only SQL if possible.

But you are right, that’s the better way.