RE: Count Query question
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.', '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 > 112010-05-01 1 > 112010-05-02 0 > 112010-05-03 3 > 112010-05-04 3 > 112010-05-05 3 > 112010-05-06 1 > 112010-05-07 0 > 112010-05-08 3 > 112010-05-09 3 > 112010-05-10 3 > 112010-05-11 3 > 112010-05-12 3 > 222010-05-01 1 > 222010-05-02 2 > 222010-05-03 0 > 222010-05-04 3 > 222010-05-05 3 > 222010-05-06 1 > 222010-05-07 0 > 222010-05-08 3 > 222010-05-09 0 > 222010-05-10 3 > 222010-05-11 3 > 222010-05-12 3 > 332010-05-01 1 > 332010-05-02 2 > 332010-05-03 3 > 332010-05-04 3 > 332010-05-05 3 > 332010-05-06 0 > 332010-05-07 0 > 332010-05-08 3 > 332010-05-09 3 > 332010-05-10 0 > 332010-05-11 3 > 332010-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...h > > 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 > >>GRO
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.', '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 > 112010-05-01 1 > 112010-05-02 0 > 112010-05-03 3 > 112010-05-04 3 > 112010-05-05 3 > 112010-05-06 1 > 112010-05-07 0 > 112010-05-08 3 > 112010-05-09 3 > 112010-05-10 3 > 112010-05-11 3 > 112010-05-12 3 > 222010-05-01 1 > 222010-05-02 2 > 222010-05-03 0 > 222010-05-04 3 > 222010-05-05 3 > 222010-05-06 1 > 222010-05-07 0 > 222010-05-08 3 > 222010-05-09 0 > 222010-05-10 3 > 222010-05-11 3 > 222010-05-12 3 > 332010-05-01 1 > 332010-05-02 2 > 332010-05-03 3 > 332010-05-04 3 > 332010-05-05 3 > 332010-05-06 0 > 332010-05-07 0 > 332010-05-08 3 > 332010-05-09 3 > 332010-05-10 0 > 332010-05-11 3 > 332010-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...h > > 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`) >
Re: Count Query question
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...h > 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
Re: Count Query question
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...h 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=bobc...@earthlink.net > > > -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Count Query question
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=bobc...@earthlink.net > -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Count Query question
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
Re: Count Query question
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. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Count Query question
With out the table definitions, I'm not sure how anyone could help. Can you send the output of "show create table" for each of the tables involved in this query? Chris W Keith Clark wrote: I'm trying to produce a report that will tell me how many products were available with a Quantity>0 before a certain date, and have that ordered by date. Table: Date Quantity Result desired DateQuantity Available May 1 5000 May 2 5050 May 3 5075 Thanks, Keith -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
