Month: January 2009

SELECT p.sku AS "Product code",
 p.annotation AS "Product Description",
  (SELECT COUNT(orders_products.fk_product_id) AS "count"
   FROM orders_products
   INNER JOIN orders 
     ON orders.id = orders_products.fk_order_id 
     AND orders.order_created >= '2008-12-01 00:00:00' 
     AND orders.order_created < = '2008-12-07 24:00:00'
   INNER JOIN products 
     ON products.id = orders_products.fk_product_id 
     AND products.sku = p.sku
   WHERE orders_products.fk_order_status_id >= 30) AS "Total number of units sold",
    (
      SELECT SUM((orders_products.price-orders_products.discount)/1.15) AS "sum"
      FROM orders_products
      INNER JOIN orders 
        ON orders.id = orders_products.fk_order_id 
        AND orders.order_created >= '2008-12-01 00:00:00'
        AND orders.order_created < = '2008-12-07 24:00:00'
      INNER JOIN products 
        ON products.id = orders_products.fk_product_id 
        AND products.sku = p.sku
      WHERE orders_products.fk_order_status_id >= 30
     ) AS "Total value"
     FROM products p
      WHERE (
        SELECT COUNT(orders_products.fk_product_id) AS "count"
        FROM orders_products
        INNER JOIN orders 
          ON orders.id = orders_products.fk_order_id 
          AND orders.order_created >= '2008-12-01 00:00:00'
          AND orders.order_created < = '2008-12-07 24:00:00'
        INNER JOIN products 
          ON products.id = orders_products.fk_product_id
          AND products.sku = p.sku
        WHERE orders_products.fk_order_status_id >= 30
      ) > 0

SELECT p.sku AS "Product code", p.annotation AS "Product Description", (SELECT COUNT(orders_products.fk_product_id) AS "count" FROM orders_products INNER JOIN orders ON orders.id = orders_products.fk_order_id AND orders.order_created >= '2008-12-01 00:00:00' AND orders.order_created < = '2008-12-07 24:00:00' INNER JOIN products ON products.id = orders_products.fk_product_id AND products.sku = p.sku WHERE orders_products.fk_order_status_id >= 30) AS "Total number of units sold", ( SELECT SUM((orders_products.price-orders_products.discount)/1.15) AS "sum" FROM orders_products INNER JOIN orders ON orders.id = orders_products.fk_order_id AND orders.order_created >= '2008-12-01 00:00:00' AND orders.order_created < = '2008-12-07 24:00:00' INNER JOIN products ON products.id = orders_products.fk_product_id AND products.sku = p.sku WHERE orders_products.fk_order_status_id >= 30 ) AS "Total value" FROM products p WHERE ( SELECT COUNT(orders_products.fk_product_id) AS "count" FROM orders_products INNER JOIN orders ON orders.id = orders_products.fk_order_id AND orders.order_created >= '2008-12-01 00:00:00' AND orders.order_created < = '2008-12-07 24:00:00' INNER JOIN products ON products.id = orders_products.fk_product_id AND products.sku = p.sku WHERE orders_products.fk_order_status_id >= 30 ) > 0

SELECT products.sku, products.annotation,
  COUNT(orders_products.fk_product_id) AS "count",
  SUM ((orders_products.price-orders_products.discount)/1.15) AS "sum"
  FROM orders_products
  INNER JOIN orders 
    ON orders.id = orders_products.fk_order_id 
    AND orders.order_created >= '2008-12-01 00:00:00' 
    AND orders.order_created < = '2008-12-07 24:00:00'
  INNER JOIN products 
    ON products.id = orders_products.fk_product_id
  WHERE orders_products.fk_order_status_id >= 30
  GROUP BY products.sku, products.annotation

SELECT products.sku, products.annotation, COUNT(orders_products.fk_product_id) AS "count", SUM ((orders_products.price-orders_products.discount)/1.15) AS "sum" FROM orders_products INNER JOIN orders ON orders.id = orders_products.fk_order_id AND orders.order_created >= '2008-12-01 00:00:00' AND orders.order_created < = '2008-12-07 24:00:00' INNER JOIN products ON products.id = orders_products.fk_product_id WHERE orders_products.fk_order_status_id >= 30 GROUP BY products.sku, products.annotation