Hi Keith, The way I would go about this is to try and pinpoint what the issue is, by breaking the query up.
For instance, if you remove the 'product_quantity > 0' condition, do you get any more rows in your result ? I also notice that your 'products_date_avaiable' is defaulting to '2008-10-01 00:00:00' which is the same date in your two sample rows. Run a query like SELECT distinct (products_date_available) FROM products and see if there are there are any other dates ... if there are only 2, then you'll get only two rows. Thanks, Justin Tifang -----Original Message----- From: Keith Clark [mailto:keithcl...@k-wbookworm.com] Sent: 13 May 2010 14:11 To: mysql@lists.mysql.com Subject: Re: Count Query question Bob, Here are a few rows of my data: 17462, 0, '0929998596', '/GraphicNovels/0929998596.jpg', '8.5000', '2010-05-12 19:02:47', '2008-10-01 00:00:00', '2008-10-01 00:00:00', '0.50', 1, 1, 7429, 0, '1', 17461, 1, '1561481912', '/Cooking/1561481912.jpg', '3.0000', '2010-05-12 19:00:17', '2008-10-01 00:00:00', '2008-10-01 00:00:00', '0.50', 1, 1, 7428, 0, '1', Here is the query I ran: SELECT date(products_date_available) as Date, COUNT(products_quantity) as 'Titles Available', SUM(products_quantity) as 'Books Available' FROM products WHERE products_quantity > 0 GROUP BY date(products_date_available); And I only got back two rows from over 2 years of daily entries: '2008-01-01', 3327, '3736' '2008-10-01', 2739, '2904' I'm not sure I understand where I'm going wrong. Keith On Wed, 2010-05-12 at 21:54 -0500, Bob Cole wrote: > 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=webmas...@lisol.co.uk __________ Information from ESET NOD32 Antivirus, version of virus signature database 5112 (20100513) __________ The message was checked by ESET NOD32 Antivirus. http://www.eset.com __________ Information from ESET NOD32 Antivirus, version of virus signature database 5112 (20100513) __________ The message was checked by ESET NOD32 Antivirus. http://www.eset.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=arch...@jab.org
