SQL query category/products

[prestashopnew]

Hi guys

 

How i can build a query to extract the sum of the products sold each month for each category, someone can help me?

 

Thank you

Edited July 14, 2016 by prestashopnew (see edit history)

[rocky]

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

[prestashopnew]

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 July 6, 2016 by prestashopnew (see edit history)

[prestashopnew]

up

[rocky]

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.

[prestashopnew]

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?

[rocky]

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.

[prestashopnew]

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

[rocky]

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).

[prestashopnew]

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

[rocky]

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.

[prestashopnew]

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 July 14, 2016 by prestashopnew (see edit history)

[rocky]

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.

[prestashopnew]

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!

[DevWL]

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 June 22, 2021 by DevWL (see edit history)