List:General Discussion« Previous MessageNext Message »
From:Bob Cole Date:May 13 2010 2:54am
Subject:Re: Count Query question
View as plain text  
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.

Thread
Count Query questionKeith Clark12 May
  • Re: Count Query questionChris W12 May
    • Re: Count Query questionKeith Clark12 May
      • Re: Count Query questionKeith Clark12 May
        • Re: Count Query questionBob Cole13 May
          • Re: Count Query questionKeith Clark13 May
            • Re: Count Query questionBob Cole13 May
              • Re: Count Query questionKeith Clark13 May
                • RE: Count Query questionwebmaster13 May