Count Query question

Count Query question

am 12.05.2010 15:39:11 von Keith Clark

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

Date Quantity 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=gcdmg-mysql-2@m.gmane.org

Re: Count Query question

am 12.05.2010 16:05:32 von Chris W

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
>
> Date Quantity 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=gcdmg-mysql-2@m.gmane.org

Re: Count Query question

am 12.05.2010 16:13:48 von Keith Clark

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=gcdmg-mysql-2@m.gmane.org

Re: Count Query question

am 12.05.2010 22:06:02 von Keith Clark

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=gcdmg-mysql-2@m.gmane.org

Re: Count Query question

am 13.05.2010 02:46:08 von Bob Cole

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=bobcole@earthlink.net
>


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql-2@m.gmane.org

Re: Count Query question

am 13.05.2010 03:00:20 von Keith Clark

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=bobcole@earthlink.net
> >
>



--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql-2@m.gmane.org

Re: Count Query question

am 13.05.2010 04:54:54 von Bob Cole

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:
>>>>=20
>>>> 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=3DMyISAM AUTO_INCREMENT=3D17418 DEFAULT CHARSET=3Dlatin1
>>>>=20
>>>> 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.
>>>>=20
>>>>=20
>>> I don't think I'm asking this question properly.
>>>=20
>>> 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.
>>>=20
>>> 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.
>>>=20
>>> 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=3Dgcdmg-mysql-2@m.gmane.o rg

Re: Count Query question

am 13.05.2010 15:11:15 von Keith Clark

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=gcdmg-mysql-2@m.gmane.org

RE: Count Query question

am 13.05.2010 16:18:00 von webmaster

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:keithclark@k-wbookworm.com]=20
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',=20

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',=20

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
>=20
> 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
>=20
> 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
>=20
> 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
>=20
> 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:
> >>>>=20
> >>>> 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=3DMyISAM AUTO_INCREMENT=3D17418 DEFAULT CHARSET=3Dlatin1
> >>>>=20
> >>>> 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.
> >>>>=20
> >>>>=20
> >>> I don't think I'm asking this question properly.
> >>>=20
> >>> 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.
> >>>=20
> >>> 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.
> >>>=20
> >>> 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.
>=20
>=20



--=20
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: =
http://lists.mysql.com/mysql?unsub=3Dwebmaster@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


=20

__________ Information from ESET NOD32 Antivirus, version of virus =
signature database 5112 (20100513) __________

The message was checked by ESET NOD32 Antivirus.

http://www.eset.com
=20


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=3Dgcdmg-mysql-2@m.gmane.o rg