Jump to content
  • Checkout
  • Login
  • Get in touch

osCommerce

The e-commerce.

Product Sales Per Month SQL Query


Trentide

Recommended Posts

Hi, thanks for looking at my post. I was interested in finding a SQL query that would tell me which products, and how many of those products have sold in a given month. I'm really not interested in finding the total sales numbers in dollars. There are many contributions for that. It seems to me this would be a good tool for myself and others to determine if a particular product is selling better than others for a given time period (maybe per month, etc. ).

 

Thanks so much,

 

Jason

Link to comment
Share on other sites

This is what I use - between 2 order numbers and with various status settings

 

SELECT  o.orders_id, op.products_name, sum(op.products_quantity * p.products_cost)
FROM orders  AS o, orders_products AS op, products AS p
WHERE o.orders_id = op.orders_id
and o.orders_id >=1 AND o.orders_id <=700
AND (o.orders_status = '3' OR o.orders_status = '2' OR
o.orders_status = '6' OR o.orders_status = '7' OR
o.orders_status = '9' OR o.orders_status = '16')
AND op.products_id = p.products_id
group BY o.orders_id, op.products_name

Now running on a fully modded, Mobile Friendly 2.3.4 Store with the Excellent MTS installed - See my profile for the mods installed ..... So much thanks for all the help given along the way by forum members.

Link to comment
Share on other sites

Thanks for the info Mort! When I put in the query, I get this:

 

#1054 - Unknown column 'p.products_cost' in 'field list'

 

Sorry I'm not very familiar with MYSQL. What did I do wrong?

 

Jason

Link to comment
Share on other sites

Ignore the code Mort-Lemur gave, that is specific to how her site is set up...

 


SELECT COUNT(op.products_id) as count, op.products_id, op.products_name
FROM orders o
LEFT JOIN orders_products op ON o.orders_id = op.orders_id
WHERE o.date_purchased BETWEEN '2012-01-01 00:00:00' AND '2012-01-31 23:59:59'
GROUP BY op.products_id
ORDER BY count DESC

 

Something like that would be close I think. Change the dates as appropriate.

 

I have not tested it.

Link to comment
Share on other sites

you can even drop the 'left' from the 'left join', an equi join is just fine

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

Link to comment
Share on other sites

BTW, if you want to learn great sql reporting, I can recommend "SQL for smarties"

http://www.amazon.com/Joe-Celkos-SQL-Smarties-Programming/dp/1558605762

it shows you ao how to create cross tabulated queries in sql, so you can have e.g. a column for each month.

I use those kinds of queries with my accounting package.

 

HTH

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

Link to comment
Share on other sites

Bear in mind that the SQL posted by burt will also include orders where the status is pending - cancelled etc as well

Now running on a fully modded, Mobile Friendly 2.3.4 Store with the Excellent MTS installed - See my profile for the mods installed ..... So much thanks for all the help given along the way by forum members.

Link to comment
Share on other sites

Wow! Thanks for all the input! I will definitely check out that link bruyndoncx! The SQL Query worked like a charm!!

 

Thanks again!

 

PS. Is there a post regarding some important sql queries on osc?

Link to comment
Share on other sites

Archived

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

×
×
  • Create New...