Kevin: I assumed the following data: products_id products_date_available products_quantity 11 2010-05-01 1 11 2010-05-02 0 11 2010-05-03 3 11 2010-05-04 3 11 2010-05-05 3 11 2010-05-06 1 11 2010-05-07 0 11 2010-05-08 3 11 2010-05-09 3 11 2010-05-10 3 11 2010-05-11 3 11 2010-05-12 3 22 2010-05-01 1 22 2010-05-02 2 22 2010-05-03 0 22 2010-05-04 3 22 2010-05-05 3 22 2010-05-06 1 22 2010-05-07 0 22 2010-05-08 3 22 2010-05-09 0 22 2010-05-10 3 22 2010-05-11 3 22 2010-05-12 3 33 2010-05-01 1 33 2010-05-02 2 33 2010-05-03 3 33 2010-05-04 3 33 2010-05-05 3 33 2010-05-06 0 33 2010-05-07 0 33 2010-05-08 3 33 2010-05-09 3 33 2010-05-10 0 33 2010-05-11 3 33 2010-05-12 3
and used the following query: SELECT products_date_available, COUNT(products_quantity), SUM(products_quantity) FROM products WHERE products_quantity > 0 GROUP BY products_date_available and got the following results: products_date_available COUNT SUM 2010-05-01 00:00:00 3 3 2010-05-02 00:00:00 2 4 2010-05-03 00:00:00 2 6 2010-05-04 00:00:00 3 9 2010-05-05 00:00:00 3 9 2010-05-06 00:00:00 2 2 2010-05-08 00:00:00 3 9 2010-05-09 00:00:00 2 6 2010-05-10 00:00:00 2 6 2010-05-11 00:00:00 3 9 2010-05-12 00:00:00 3 9 One line for each day except that 2010-05-07 is missing because each product had 0 quantity on that day. For example, on 2010-05-01, there were 3 products (each with a quantity of 1) for a total quantity of 3. I wonder if I am representing your situation correctly. What am I missing? Bob On May 12, 2010, at 8:00 PM, Keith Clark wrote: > Hi Bob, > No, actually it does not. I'm looking for the count of items. From > your query example I only get two rows. This table has over 2 1/2 years > of daily sales data. > Maybe I'm not stating my question correctly...hmmmm.... > Thanks for responding though, greatly appreciated. > Keith > On Wed, 2010-05-12 at 19:46 -0500, Bob Cole wrote: >> Keith: >> Does this work? >> SELECT products_date_available, COUNT(products_quantity) >> FROM products >> WHERE products_quantity > 0 >> GROUP BY products_date_available >> Hope this helps, >> Bob >> On May 12, 2010, at 3:06 PM, Keith Clark wrote: >>> On Wed, 2010-05-12 at 10:13 -0400, Keith Clark wrote: >>>> Chris, >>>> Here is my full table definition: >>>> >>>> CREATE TABLE `products` ( >>>> `products_id` int(15) NOT NULL AUTO_INCREMENT, >>>> `products_quantity` int(4) NOT NULL, >>>> `products_model` varchar(15) NOT NULL DEFAULT '', >>>> `products_image` varchar(64) DEFAULT NULL, >>>> `products_price` decimal(15,4) DEFAULT NULL, >>>> `products_date_added` timestamp NULL DEFAULT CURRENT_TIMESTAMP, >>>> `products_last_modified` datetime DEFAULT '2008-10-01 00:00:00', >>>> `products_date_available` datetime DEFAULT '2008-10-01 00:00:00', >>>> `products_weight` decimal(5,2) DEFAULT '0.50', >>>> `products_status` tinyint(1) NOT NULL DEFAULT '1', >>>> `products_tax_class_id` int(11) DEFAULT '1', >>>> `manufacturers_id` int(11) DEFAULT NULL, >>>> `products_ordered` int(11) DEFAULT '0', >>>> `products_format` varchar(20) DEFAULT NULL, >>>> `abebooks_price` decimal(15,4) DEFAULT NULL, >>>> PRIMARY KEY (`products_id`,`products_model`), >>>> UNIQUE KEY `products_model` (`products_model`), >>>> KEY `idx_products_date_added` (`products_date_added`), >>>> KEY `manufacturers_id` (`manufacturers_id`) >>>> ) ENGINE=MyISAM AUTO_INCREMENT=17418 DEFAULT CHARSET=latin1 >>>> >>>> So, I'd like to create a report that grouped by products_date_available, >>>> counts all records before products_date_available with a >>>> products_quantity>0. >>>> >>>> >>> I don't think I'm asking this question properly. >>> >>> For every date in products_date_available in the table, I'd like to know >>> the count of items available with products_quantity>0 up until that >>> date. >>> >>> So if there are 500 days in the table, there should be 500 rows in the >>> report. Each showing the products available as of that date in time. >>> >>> I hope that clarifies it. I can write a query to do so for each >>> individual date, just not a report for all dates at the same time. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=arch...@jab.org