Hi,
I'm trying to write a sql query that will give me the total price for all products in the opencart shop. So for example if I had 100 products in the shop 50 of them for £1 each and 50 of them for £2 each, the total price of all the products in the shop would be £150. Any ideas on how I can do this?
SQL (assumes no database prefix):
OpenCart:
Code: Select all
SELECT SUM(price) FROM product;Code: Select all
$this->db->query("SELECT SUM(price) FROM `" . DB_PREFIX . "product`");-Ryan
Ignoring specials, something like this should work:
Taking specials into account:
Good luck.
Midgette
Code: Select all
echo $this->db->query("select CONCAT('$', FORMAT(sum(quantity * price), 2)) as total_stock_amt from " . DB_PREFIX . "product")->row['total_stock_amt'];Code: Select all
echo $this->db->query("
select CONCAT('$', FORMAT(sum(p.`quantity` * case when ps.Price is null then p.`price` else ps.price end), 2)) as total_stock_amt
from " . DB_PREFIX . "product p LEFT JOIN " . DB_PREFIX . "product_special ps on p.product_id = ps.product_id
where case when ps.Price is null then 1 = 1 else ps.date_start <= curdate() and ps.date_end >= curdate() end ")->row['total_stock_amt'];
Midgette
Who is online
Users browsing this forum: No registered users and 29 guests
