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`)
) 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_quantity0.
I
