SQL Query for amount of products sold within dates?

0
votes

Fiddle Here: http://sqlfiddle.com/#!2/2f85f4/1

I am trying to create an 'inventory demand' report essentially; what products were sold between X and Y and how much of them.

The closest I can get is this query, but this doesn't return the correct math.

Can someone please offer some guidance?

SELECT orders_products.op_products_name
     , orders_products.op_products_id
     , SUM(orders_products.op_products_qty) AS TotalSold 
  FROM orders_products
     , orders 
 WHERE orders.orders_date_purchased
   AND orders.orders_date_purchased BETWEEN '2012-11-05 00:00:00' AND '2012-11-10 00:00:00'
 GROUP 
    BY orders_products.op_products_id 
 ORDER 
    BY TotalSold DESC

The schema is available here:

--

-- Table structure for table orders

CREATE TABLE IF NOT EXISTS orders ( orders_ID bigint(20) unsigned NOT NULL AUTO_INCREMENT, orders_date_purchased timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP, orders_delivery_name varchar(100) NOT NULL, orders_delivery_company_name varchar(100) NOT NULL, orders_delivery_address varchar(255) NOT NULL, orders_delivery_city varchar(100) NOT NULL, orders_delivery_state char(2) NOT NULL, orders_delivery_zipcode char(5) NOT NULL, orders_delivery_phone char(10) NOT NULL, orders_delivery_email varchar(100) NOT NULL, orders_billing_name varchar(120) DEFAULT NULL, orders_billing_address varchar(255) DEFAULT NULL, orders_billing_city varchar(100) DEFAULT NULL, orders_billing_state char(2) DEFAULT NULL, orders_billing_zipcode char(5) DEFAULT NULL, orders_billing_phone char(10) DEFAULT NULL, orders_billing_email varchar(100) DEFAULT NULL, orders_users_ID bigint(20) NOT NULL, orders_distributor_ID bigint(20) DEFAULT NULL, orders_affiliate_ID bigint(20) DEFAULT NULL, orders_sales_tax decimal(11,2) NOT NULL, orders_discount_applied decimal(11,2) DEFAULT NULL, orders_ip_address char(15) NOT NULL, orders_shipping_method varchar(255) NOT NULL, orders_payment_method int(10) unsigned NOT NULL, orders_order_total decimal(11,2) NOT NULL, orders_shipping_cost decimal(11,2) DEFAULT NULL, orders_total_saved decimal(11,2) NOT NULL, orders_placed_by bigint(20) NOT NULL, notes blob, orders_inv_status int(11) DEFAULT NULL, orders_date_modified timestamp NULL DEFAULT NULL, orders_process_status int(11) DEFAULT '1', PRIMARY KEY (orders_ID) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=2379 ;

--

-- Dumping data for table orders

INSERT INTO orders (orders_ID, orders_date_purchased, orders_delivery_name, orders_delivery_company_name, orders_delivery_address, orders_delivery_city, orders_delivery_state, orders_delivery_zipcode, orders_delivery_phone, orders_delivery_email, orders_billing_name, orders_billing_address, orders_billing_city, orders_billing_state, orders_billing_zipcode, orders_billing_phone, orders_billing_email, orders_users_ID, orders_distributor_ID, orders_affiliate_ID, orders_sales_tax, orders_discount_applied, orders_ip_address, orders_shipping_method, orders_payment_method, orders_order_total, orders_shipping_cost, orders_total_saved, orders_placed_by, notes, orders_inv_status, orders_date_modified, orders_process_status) VALUES (1, '2012-11-05 19:58:12', 'John Smith', '', '123 Manatee Street', 'Navarre', 'FL', '32566', '8508675309', '[email protected]', NULL, NULL, NULL, NULL, NULL, NULL, NULL, 83, NULL, NULL, 0.06, NULL, '00.000.000.00', 'in_person_delivery', 9, 61.59, 0.00, 0.00, 0, NULL, 8, NULL, 3), (2, '2012-11-06 01:05:20', 'Judy Richards', '', '456 Devmor Ct', 'Navarre', 'FL', '32566', '8508675309', '[email protected]', NULL, NULL, NULL, NULL, NULL, NULL, NULL, 87, NULL, NULL, 0.06, NULL, '00.000.000.00', 'in_person_delivery', 9, 158.97, 0.00, 0.00, 0, NULL, 8, NULL, 3), (4, '2012-11-08 04:32:23', 'John Smith', '', '123 Manatee Street', 'Navarre', 'FL', '32578', '8508675309', '[email protected]', NULL, NULL, NULL, NULL, NULL, NULL, NULL, 92, NULL, NULL, 0.06, NULL, '00.000.000.00', 'in_person_delivery', 9, 52.99, NULL, 0.00, 0, NULL, 8, '0000-00-00 00:00:00', 3), (5, '2012-11-09 00:11:54', 'Adam Davis', '', '4307 D134 Legendary Dr.', 'Navarre', 'FL', '32541', '8508675309', '[email protected]', NULL, NULL, NULL, NULL, NULL, NULL, NULL, 89, 84, 0, 0.06, NULL, '00.000.000.00', 'in_person_delivery', 9, 160.51, 0.00, 0.00, 0, NULL, 8, NULL, 3), (6, '2012-11-09 21:14:25', 'Judy Sterling', '', '2310 Lexington Lane', 'Navarre', 'FL', '32566', '8508675309', '[email protected]', NULL, NULL, NULL, NULL, NULL, NULL, NULL, 87, NULL, NULL, 0.06, NULL, '00.000.000.00', 'in_person_delivery', 9, 158.97, 0.00, 0.00, 0, NULL, 8, NULL, 3);

--

-- Table structure for table orders_products

CREATE TABLE IF NOT EXISTS orders_products ( orders_products_ID bigint(20) NOT NULL AUTO_INCREMENT, op_order_id bigint(20) unsigned NOT NULL, op_products_id bigint(20) unsigned NOT NULL, op_products_mfr_part_number varchar(65) NOT NULL, op_products_name varchar(15) NOT NULL, op_products_qty bigint(10) unsigned NOT NULL, PRIMARY KEY (orders_products_ID) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=5923 ;

--

-- Dumping data for table orders_products

INSERT INTO orders_products (orders_products_ID, op_order_id, op_products_id, op_products_mfr_part_number, op_products_name, op_products_qty) VALUES (1, 1, 491, 'CWP8489A001BA', 'X25', 1), (2, 2, 2134, 'Professional Fee', 'Professional Fe', 3), (3, 3, 473, 'CWPDR360', 'DR-360', 1), (4, 4, 2134, 'Professional Fee', 'Professional Fe', 1), (5, 5, 362, 'CWPDR360', 'DR-360', 1);

--

-- Table structure for table products

CREATE TABLE IF NOT EXISTS products ( products_id bigint(20) NOT NULL AUTO_INCREMENT, products_brand varchar(200) NOT NULL, products_brand_type varchar(200) NOT NULL, products_mfr_part_number varchar(65) NOT NULL, products_common_name varchar(15) NOT NULL, products_msrp decimal(11,2) NOT NULL, products_price decimal(11,2) NOT NULL, products_description varchar(255) NOT NULL, products_weight decimal(11,2) NOT NULL DEFAULT '1.00', products_length decimal(3,2) DEFAULT NULL, products_width decimal(3,2) DEFAULT NULL, products_height decimal(3,2) DEFAULT NULL, products_tax_exempt int(11) DEFAULT NULL, PRIMARY KEY (products_id) ) ENGINE=MyISAM DEFAULT CHARSET=utf8 AUTO_INCREMENT=2558 ;

--

-- Dumping data for table products

INSERT INTO products (products_id, products_brand, products_brand_type, products_mfr_part_number, products_common_name, products_msrp, products_price, products_description, products_weight, products_length, products_width, products_height, products_tax_exempt) VALUES (1, 'HP', 'Original', '92298A ', '98A', 132.99, 131.66, 'Genuine HP® LaserJet 98A Black Toner Cartridge (92298A) ', 4.20, 9.99, 6.14, 9.72, NULL), (2, 'HP', 'Original', 'C3903A ', '03A', 112.99, 111.86, 'Genuine HP® LaserJet 03A Black Toner Cartridge (C3903A) ', 3.00, 9.99, 4.80, 7.72, NULL), (3, 'HP', 'Original', 'C3906A ', '06A', 87.99, 87.11, 'Genuine HP® LaserJet 06A Black Toner Cartridge (C3906A)', 2.13, 9.99, 5.16, 6.50, NULL), (4, 'HP', 'Original', 'C3909A', '09A', 254.99, 252.44, 'Genuine HP® LaserJet 09A Black Toner Cartridge (C3909A)', 3.31, 9.99, 4.72, 8.07, NULL), (5, 'HP', 'Original', 'C4092A ', '92A', 75.99, 75.23, 'Genuine HP® LaserJet 92A Black Toner Cartridge (C4092A) ', 2.20, 9.99, 5.16, 6.50, NULL);

1
mysqlsqljoincount
Can you provide the desired result set? – Strawberry
BTW, your naming policy is nuts. – Strawberry

1 Answers

1
votes

The major problem is that you need a join condition between orders_products and orders. You have a , which is equivalent to cross join. As a simple rule: never use commas in the from clause.

In addition, the where clause was awkwardly phrased. Here is a rewrite of your query that also uses table aliases for readability:

SELECT op.op_products_name, op.op_products_id, SUM(op.op_products_qty) AS TotalSold
FROM orders_products op JOIN
     orders o
     ON op.op_order_id = o.orders_id
WHERE o.orders_date_purchased BETWEEN '2012-11-05 00:00:00' AND '2012-11-10 00:00:00'
GROUP BY op.op_products_id
ORDER BY TotalSold DESC;