Jump to content

SQL query category/products


prestashopnew
 Share

Recommended Posts

Is there a reason you're not using Stats > Best categories in the Back Office to get this information?

 

Yeah, I need to know the actual turnover, in that section also calculates the returned product, the order canceled and something else. For example, he says I have gained about € 3k but it's not true, I have gained much less.

Edited by prestashopnew (see edit history)

Share this post


Link to post
Share on other sites

Try something like the following:

SELECT `id_category`, COUNT(*)
FROM `ps_order_detail` od
LEFT JOIN `ps_orders` o ON (od.`id_order` = o.`id_order`)
LEFT JOIN `ps_category_product` cp ON od.`product_id` = cp.`id_product`
WHERE o.`date_add` BETWEEN '2016-07-01' AND '2016-07-31'
GROUP BY `id_category` 

Change ps_ to your database prefix. This should get the products from all orders for July 2016, then match the products up with each category they are in, then group then by category and get the category ID along with the count of the number of products.

 

I hope it helps.

Share this post


Link to post
Share on other sites

Try something like the following:

SELECT `id_category`, COUNT(*)
FROM `ps_order_detail` od
LEFT JOIN `ps_orders` o ON (od.`id_order` = o.`id_order`)
LEFT JOIN `ps_category_product` cp ON od.`product_id` = cp.`id_product`
WHERE o.`date_add` BETWEEN '2016-07-01' AND '2016-07-31'
GROUP BY `id_category` 

Change ps_ to your database prefix. This should get the products from all orders for July 2016, then match the products up with each category they are in, then group then by category and get the category ID along with the count of the number of products.

 

I hope it helps.

 

Thanks for the reply but I can not understand the result of this query.
 
COUNT number for each category is too high, perhaps calculates canceled orders and abandoned carts too.
 
Is possibile to know only the real / actual sales?

Share this post


Link to post
Share on other sites

Yes, the query includes cancelled orders, but not abandoned carts. I'll need to create a more complicated query that excludes orders with the "Cancelled" status.

Thank you man, and sorry for your time. 

Take your time. I just wait

Share this post


Link to post
Share on other sites

Try this one:

SELECT `id_category`, COUNT(*) as `num_sales`
FROM `ps_order_detail` od
LEFT JOIN `ps_orders` o ON (od.`id_order` = o.`id_order`)
LEFT JOIN `ps_category_product` cp ON od.`product_id` = cp.`id_product`
WHERE `date_add` BETWEEN '2016-07-01' AND '2016-07-31' AND o.`id_order` NOT IN (
    SELECT `id_order`
    FROM `ps_order_history`
    WHERE `id_order_state` = 6) 
GROUP BY `id_category`

It adds a extra condition that excludes orders that have ever had a "Canceled" state (which has an ID of 6 by default).

Share this post


Link to post
Share on other sites

Try this one:

SELECT `id_category`, COUNT(*) as `num_sales`
FROM `ps_order_detail` od
LEFT JOIN `ps_orders` o ON (od.`id_order` = o.`id_order`)
LEFT JOIN `ps_category_product` cp ON od.`product_id` = cp.`id_product`
WHERE `date_add` BETWEEN '2016-07-01' AND '2016-07-31' AND o.`id_order` NOT IN (
    SELECT `id_order`
    FROM `ps_order_history`
    WHERE `id_order_state` = 6) 
GROUP BY `id_category`

It adds a extra condition that excludes orders that have ever had a "Canceled" state (which has an ID of 6 by default).

 

Thanks for your help, but unfortunately still gives me the incorrect result, don't know why
 
I have a question, how do you select column "id_category" from "ps_order_detail"?, this column does not exist in this table

Share this post


Link to post
Share on other sites

I'm not sure why it isn't working. Do you have orders that are only partially cancelled or orders that are cancelled and then uncancelled?

 

I'm joining the `ps_order_detail` table with the `ps_category_product` table and then getting the `id_category` from that table. That means the product is being counted once for each category it appears in. So if a product is purchased once and that product is in category 4 and category 5, you'll see "4, 1" and "5, 1" in the results. I can use the product's default category instead if you only wanted it counted once.

  • Like 1

Share this post


Link to post
Share on other sites

SELECT `id_category`, COUNT(*) as `num_sales`
FROM `ps_order_detail` od
LEFT JOIN `ps_orders` o ON (od.`id_order` = o.`id_order`)
LEFT JOIN `ps_category_product` cp ON od.`product_id` = cp.`id_product`
WHERE `date_add` BETWEEN '2016-07-01' AND '2016-07-31' AND o.`id_order` NOT IN (
    SELECT `id_order`
    FROM `ps_order_history`
    WHERE `id_order_state` = 6) 
GROUP BY `id_category`

I'm working with your query, really thanks.

 

But i'm using `date_upd`   instead of   `date_add`

and 'ps_orders.current_state = 5'   instead of   'id_order_state = 6'

 

I'm getting better results

 

Now, i would like to use 'product_reference' and not 'id_category' to get results, there is a way to "sum" all products with similar reference? that isn't INT

 

For example, if i use product_reference, the result is something like it:
 
product_reference                  result
BR_0252_PS                          1
BR_0311L_TI                          1
BR_0350E_TI / FU-GRI          1
BR_0350E_TI / ROSA-GRI     1
 
I would like to sum all the "BR_"  to get the total of these on one line.
   
Product_reference  TOTAL
BR_                         4
Edited by prestashopnew (see edit history)

Share this post


Link to post
Share on other sites

This query is getting too complicated. ;)

 

Try:

SELECT substr(p.`reference`, 1, 3), COUNT(*) as `num_sales`
FROM `ps_order_detail` od
LEFT JOIN `ps_orders` o ON (od.`id_order` = o.`id_order`)
LEFT JOIN `ps_product` p ON od.`product_id` = p.`id_product`
WHERE o.`date_add` BETWEEN '2016-07-01' AND '2016-07-31' AND o.`id_order` NOT IN (
    SELECT `id_order`
    FROM `ps_order_history`
    WHERE `id_order_state` = 6) 
GROUP BY substr(p.`reference`, 1, 3)

This will group all products by the first three characters of each product reference.

  • Like 1

Share this post


Link to post
Share on other sites

This query is getting too complicated. ;)

 

Try:

SELECT substr(p.`reference`, 1, 3), COUNT(*) as `num_sales`
FROM `ps_order_detail` od
LEFT JOIN `ps_orders` o ON (od.`id_order` = o.`id_order`)
LEFT JOIN `ps_product` p ON od.`product_id` = p.`id_product`
WHERE o.`date_add` BETWEEN '2016-07-01' AND '2016-07-31' AND o.`id_order` NOT IN (
    SELECT `id_order`
    FROM `ps_order_history`
    WHERE `id_order_state` = 6) 
GROUP BY substr(p.`reference`, 1, 3)

This will group all products by the first three characters of each product reference.

Thank you bro, you rock! :)

  • Like 1

Share this post


Link to post
Share on other sites

  • 4 years later...
On 7/15/2016 at 5:21 PM, prestashopnew said:

SELECT substr(p.`reference`, 1, 3), COUNT(*) as `num_sales` FROM `ps_order_detail` od LEFT JOIN `ps_orders` o ON (od.`id_order` = o.`id_order`) LEFT JOIN `ps_product` p ON od.`product_id` = p.`id_product` WHERE o.`date_add` BETWEEN '2016-07-01' AND '2016-07-31' AND o.`id_order` NOT IN ( SELECT `id_order` FROM `ps_order_history` WHERE `id_order_state` = 6) GROUP BY substr(p.`reference`, 1, 3)

You can also grup by years and months - this way you will not have to run this query for every month.
This is quite simple to do. Consider the fallowing SQL:

 

SELECT CONCAT(YEAR(o.invoice_date),' - ', MONTH(o.invoice_date)), substr(p.`reference`, 1, 3), COUNT(*) as `num_sales` 
FROM `ps_order_detail` od 
LEFT JOIN `ps_orders` o ON (od.`id_order` = o.`id_order`) 
LEFT JOIN `ps_product` p ON od.`product_id` = p.`id_product` 
WHERE o.`date_add` BETWEEN '2016-07-01' AND '2050-07-31' 
AND o.`id_order` NOT IN ( 
  	SELECT `id_order` 
  	FROM `ps_order_history` 
  	WHERE `id_order_state` = 6
	) 
GROUP BY CONCAT(YEAR(o.invoice_date),' - ', MONTH(o.invoice_date)), substr(p.`reference`, 1, 3)

 

Edited by DevWL (see edit history)

Share this post


Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
 Share

×
×
  • Create New...

Important Information

Cookies ensure the smooth running of our services. Using these, you accept the use of cookies. Learn More