Edit History

I would like to know how I can see the total in money of the products I have in stock, including quantities, combinations of all products.
The customer would like to know the total value of the goods available.

SQL QUERY in Catalog-->products (show from prestashop frontend)

Solution (partial):

EDIT: - -

this sql doesn't work, because if i change the quantity it doesn't update the total price!!!

Quote

 

SELECT SQL_CALC_FOUND_ROWS p.`id_product`  AS `id_product`,

SUM(sa.`price`) AS `totalMoney`
 
FROM  `ps_product` p 
 LEFT JOIN `ps_product_lang` pl ON (pl.`id_product` = p.`id_product` AND pl.`id_lang` = 1 AND pl.`id_shop` = 1) 
 LEFT JOIN `ps_stock_available` sav ON (sav.`id_product` = p.`id_product` AND sav.`id_product_attribute` = 0 AND sav.id_shop = 1  AND sav.id_shop_group = 0 ) 
 JOIN `ps_product_shop` sa ON (p.`id_product` = sa.`id_product` AND sa.id_shop = 1) 
 LEFT JOIN `ps_category_lang` cl ON (sa.`id_category_default` = cl.`id_category` AND cl.`id_lang` = 1 AND cl.id_shop = 1) 
 LEFT JOIN `ps_category` c ON (c.`id_category` = cl.`id_category`) 
 LEFT JOIN `ps_shop` shop ON (shop.id_shop = 1) 
 LEFT JOIN `ps_image_shop` image_shop ON (image_shop.`id_product` = p.`id_product` AND image_shop.`cover` = 1 AND image_shop.id_shop = 1) 
 LEFT JOIN `ps_image` i ON (i.`id_image` = image_shop.`id_image`) 
 LEFT JOIN `ps_product_download` pd ON (pd.`id_product` = p.`id_product`) 
WHERE (1 AND sav.`quantity`  >=1)
 
ORDER BY  `id_product` desc

 

I would like to know how I can see the total in money of the products I have in stock, including quantities, combinations of all products.
The customer would like to know the total value of the goods available.

SQL QUERY in Catalog-->products (show from prestashop frontend)

Solution:

SELECT SQL_CALC_FOUND_ROWS p.`id_product`  AS `id_product`,

SUM(sa.`price`) AS `totalMoney`
 
FROM  `ps_product` p 
 LEFT JOIN `ps_product_lang` pl ON (pl.`id_product` = p.`id_product` AND pl.`id_lang` = 1 AND pl.`id_shop` = 1) 
 LEFT JOIN `ps_stock_available` sav ON (sav.`id_product` = p.`id_product` AND sav.`id_product_attribute` = 0 AND sav.id_shop = 1  AND sav.id_shop_group = 0 ) 
 JOIN `ps_product_shop` sa ON (p.`id_product` = sa.`id_product` AND sa.id_shop = 1) 
 LEFT JOIN `ps_category_lang` cl ON (sa.`id_category_default` = cl.`id_category` AND cl.`id_lang` = 1 AND cl.id_shop = 1) 
 LEFT JOIN `ps_category` c ON (c.`id_category` = cl.`id_category`) 
 LEFT JOIN `ps_shop` shop ON (shop.id_shop = 1) 
 LEFT JOIN `ps_image_shop` image_shop ON (image_shop.`id_product` = p.`id_product` AND image_shop.`cover` = 1 AND image_shop.id_shop = 1) 
 LEFT JOIN `ps_image` i ON (i.`id_image` = image_shop.`id_image`) 
 LEFT JOIN `ps_product_download` pd ON (pd.`id_product` = p.`id_product`) 
WHERE (1 AND sav.`quantity`  >=1)
 
ORDER BY  `id_product` desc

I would like to know how I can see the total in money of the products I have in stock, including quantities, combinations of all products.
The customer would like to know the total value of the goods available.

SQL QUERY in Catalog-->products (show from prestashop frontend)
I have to put SUM*QUANTITY FUNCTION:

SELECT SQL_CALC_FOUND_ROWS p.`id_product`  AS `id_product`,

/////     SUM(sav.`quantity`* sa.`price`) AS 'totalMoney',    /////   ?????

 p.`reference`  AS `reference`,
 sa.`price`  AS `price`,
 p.`id_shop_default`  AS `id_shop_default`,
 p.`is_virtual`  AS `is_virtual`,
 pl.`name`  AS `name`,
 pl.`link_rewrite`  AS `link_rewrite`,
 sa.`active`  AS `active`,
 shop.`name`  AS `shopname`,
 image_shop.`id_image`  AS `id_image`,
 cl.`name`  AS `name_category`,
 0 AS `price_final`,
 pd.`nb_downloadable`  AS `nb_downloadable`,
 sav.`quantity`  AS `sav_quantity`,
 IF(sav.`quantity`<=0, 1, 0) AS `badge_danger` 
FROM  `ps_product` p 
 LEFT JOIN `ps_product_lang` pl ON (pl.`id_product` = p.`id_product` AND pl.`id_lang` = 1 AND pl.`id_shop` = 1) 
 LEFT JOIN `ps_stock_available` sav ON (sav.`id_product` = p.`id_product` AND sav.`id_product_attribute` = 0 AND sav.id_shop = 1  AND sav.id_shop_group = 0 ) 
 JOIN `ps_product_shop` sa ON (p.`id_product` = sa.`id_product` AND sa.id_shop = 1) 
 LEFT JOIN `ps_category_lang` cl ON (sa.`id_category_default` = cl.`id_category` AND cl.`id_lang` = 1 AND cl.id_shop = 1) 
 LEFT JOIN `ps_category` c ON (c.`id_category` = cl.`id_category`) 
 LEFT JOIN `ps_shop` shop ON (shop.id_shop = 1) 
 LEFT JOIN `ps_image_shop` image_shop ON (image_shop.`id_product` = p.`id_product` AND image_shop.`cover` = 1 AND image_shop.id_shop = 1) 
 LEFT JOIN `ps_image` i ON (i.`id_image` = image_shop.`id_image`) 
 LEFT JOIN `ps_product_download` pd ON (pd.`id_product` = p.`id_product`) 
WHERE (1 AND state = 1)
 
ORDER BY  `id_product` desc

Thx for the help

.

I would like to know how I can see the total in money of the products I have in stock, including quantities, combinations of all products.
The customer would like to know the total value of the goods available.

SQL QUERY in Catalog-->products (show from prestashop frontend)
I have to put SUM*QUANTITY FUNCTION:

SELECT SQL_CALC_FOUND_ROWS p.`id_product`  AS `id_product`,

/////     SUM(sav.`quantity`* sa.`price`) AS 'totalMoney',    /////   ?????

 p.`reference`  AS `reference`,
 sa.`price`  AS `price`,
 p.`id_shop_default`  AS `id_shop_default`,
 p.`is_virtual`  AS `is_virtual`,
 pl.`name`  AS `name`,
 pl.`link_rewrite`  AS `link_rewrite`,
 sa.`active`  AS `active`,
 shop.`name`  AS `shopname`,
 image_shop.`id_image`  AS `id_image`,
 cl.`name`  AS `name_category`,
 0 AS `price_final`,
 pd.`nb_downloadable`  AS `nb_downloadable`,
 sav.`quantity`  AS `sav_quantity`,
 IF(sav.`quantity`<=0, 1, 0) AS `badge_danger` 
FROM  `ps_product` p 
 LEFT JOIN `ps_product_lang` pl ON (pl.`id_product` = p.`id_product` AND pl.`id_lang` = 1 AND pl.`id_shop` = 1) 
 LEFT JOIN `ps_stock_available` sav ON (sav.`id_product` = p.`id_product` AND sav.`id_product_attribute` = 0 AND sav.id_shop = 1  AND sav.id_shop_group = 0 ) 
 JOIN `ps_product_shop` sa ON (p.`id_product` = sa.`id_product` AND sa.id_shop = 1) 
 LEFT JOIN `ps_category_lang` cl ON (sa.`id_category_default` = cl.`id_category` AND cl.`id_lang` = 1 AND cl.id_shop = 1) 
 LEFT JOIN `ps_category` c ON (c.`id_category` = cl.`id_category`) 
 LEFT JOIN `ps_shop` shop ON (shop.id_shop = 1) 
 LEFT JOIN `ps_image_shop` image_shop ON (image_shop.`id_product` = p.`id_product` AND image_shop.`cover` = 1 AND image_shop.id_shop = 1) 
 LEFT JOIN `ps_image` i ON (i.`id_image` = image_shop.`id_image`) 
 LEFT JOIN `ps_product_download` pd ON (pd.`id_product` = p.`id_product`) 
WHERE (1 AND state = 1)
 
ORDER BY  `id_product` desc

Thx for the help

.

I would like to know how I can see the total in money of the products I have in stock, including quantities, combinations of all products.
The customer would like to know the total value of the goods available.

SQL QUERY in Catalog-->products (show from prestashop frontend)
I have to put SUM*QUANTITY FUNCTION:

SELECT SQL_CALC_FOUND_ROWS p.`id_product`  AS `id_product`,
 p.`reference`  AS `reference`,
 sa.`price`  AS `price`,
 p.`id_shop_default`  AS `id_shop_default`,
 p.`is_virtual`  AS `is_virtual`,
 pl.`name`  AS `name`,
 pl.`link_rewrite`  AS `link_rewrite`,
 sa.`active`  AS `active`,
 shop.`name`  AS `shopname`,
 image_shop.`id_image`  AS `id_image`,
 cl.`name`  AS `name_category`,
 0 AS `price_final`,
 pd.`nb_downloadable`  AS `nb_downloadable`,
 sav.`quantity`  AS `sav_quantity`,
 IF(sav.`quantity`<=0, 1, 0) AS `badge_danger` 
FROM  `ps_product` p 
 LEFT JOIN `ps_product_lang` pl ON (pl.`id_product` = p.`id_product` AND pl.`id_lang` = 1 AND pl.`id_shop` = 1) 
 LEFT JOIN `ps_stock_available` sav ON (sav.`id_product` = p.`id_product` AND sav.`id_product_attribute` = 0 AND sav.id_shop = 1  AND sav.id_shop_group = 0 ) 
 JOIN `ps_product_shop` sa ON (p.`id_product` = sa.`id_product` AND sa.id_shop = 1) 
 LEFT JOIN `ps_category_lang` cl ON (sa.`id_category_default` = cl.`id_category` AND cl.`id_lang` = 1 AND cl.id_shop = 1) 
 LEFT JOIN `ps_category` c ON (c.`id_category` = cl.`id_category`) 
 LEFT JOIN `ps_shop` shop ON (shop.id_shop = 1) 
 LEFT JOIN `ps_image_shop` image_shop ON (image_shop.`id_product` = p.`id_product` AND image_shop.`cover` = 1 AND image_shop.id_shop = 1) 
 LEFT JOIN `ps_image` i ON (i.`id_image` = image_shop.`id_image`) 
 LEFT JOIN `ps_product_download` pd ON (pd.`id_product` = p.`id_product`) 
WHERE (1 AND state = 1)
 
ORDER BY  `id_product` desc

Thx for the help

.