[1.4.8/1.4.9] Products Purchased Report

Code: Select all

SELECT
  order_product.name,
  order_product.model,
  order_product.price,
  SUM(order_product.quantity) AS Qty,
  COUNT(order_product.product_id) AS Orders,
  SUM(order_product.total) AS Total,
  `order`.order_status_id
FROM
  `order`
  INNER JOIN order_product
    ON `order`.order_id = order_product.order_id
WHERE
  `order`.order_status_id > 0
GROUP BY
  order_product.name
ORDER BY
  order_product.name

Code: Select all

SELECT
  order_product.name, order_product.model,
  SUM(order_product.quantity) AS Quantity,
  SUM(order_product.total) AS Total,
  `order`.order_status_id, order_product.product_id
FROM  `order` INNER JOIN order_product ON `order`.order_id = order_product.order_id
WHERE `order`.order_status_id > 0
GROUP BY order_product.product_id
ORDER BY order_product.name

Code: Select all

$query = $this->db->query("SELECT op.name, op.model, SUM(op.quantity) AS quantity, SUM(op.total + op.tax) AS total FROM `" . DB_PREFIX . "order` o LEFT JOIN " . DB_PREFIX . "order_product op ON (op.order_id = o.order_id) WHERE o.order_status_id > '0' GROUP BY model ORDER BY total DESC LIMIT " . (int)$start . "," . (int)$limit);

Code: Select all

$query = $this->db->query("SELECT op.name, op.model, SUM(op.quantity) AS quantity, SUM(op.total+op.total*op.tax/100) AS total FROM `" . DB_PREFIX . "order` o INNER JOIN " . DB_PREFIX . "order_product op ON (op.order_id = o.order_id) WHERE o.order_status_id > 0 GROUP BY op.product_id ORDER BY total DESC LIMIT " . (int)$start . "," . (int)$limit);

Code: Select all

$query = $this->db->query("SELECT * FROM `" . DB_PREFIX . "order_product` GROUP BY model");

Code: Select all

$query = $this->db->query("SELECT * FROM `" . DB_PREFIX . "order_product` GROUP BY product_id");