Re: Count Query question

2010-05-13 Thread 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.', '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

RE: Count Query question

2010-05-13 Thread 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: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
 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

Count Query question

2010-05-12 Thread Keith Clark 
I'm trying to produce a report that will tell me how many products were
available with a Quantity0 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

Re: Count Query question

2010-05-12 Thread 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 Quantity0 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

Re: Count Query question

2010-05-12 Thread 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_quantity0.





-- 
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

2010-05-12 Thread 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_quantity0.
 
 
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_quantity0 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

2010-05-12 Thread 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_quantity0.
 
 
 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_quantity0 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

2010-05-12 Thread 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...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 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_quantity0 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

2010-05-12 Thread 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...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 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_quantity0 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

Newbie group/count query question

2001-05-01 Thread Graham Nichols 

I have a table which contains a date column and an order_number column. I
need to formulate a query syntax to return the total number of orders for
each day in a given month (if any). Can someone help me with the syntax
please as I've been stumbling around with it all day without success.

Many thanks,   Graham



-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, e-mail [EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

RE: Newbie group/count query question

2001-05-01 Thread Ravi Raman 

hi.

your table looks something like this:

table1
-
date(date)
order_number int(6)

...i wasn't sure if order_number is referring to a order table
somewhere...
if there are multiple rows for each day, and you want to add up
order_number for each day, use:

  select
DAYOFMONTH(date) as d,
SUM(order_number)
  from
table1
  group by d

if you want to count the rows per day (which i think is what you're after),
use:

  select
DAYOFMONTH(date) as d,
count(order_number)
  from
table1
  group by d

you probably want to add in a where clause

  where MONTH(date) = 4

to limit the rows to all days in april, for example.

hth.
-ravi.

-Original Message-
From: Graham Nichols [mailto:[EMAIL PROTECTED]]
Sent: Tuesday, May 01, 2001 11:23 AM
To: [EMAIL PROTECTED]
Subject: Newbie group/count query question


I have a table which contains a date column and an order_number column. I
need to formulate a query syntax to return the total number of orders for
each day in a given month (if any). Can someone help me with the syntax
please as I've been stumbling around with it all day without success.

Many thanks,   Graham



-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, e-mail [EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php



-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, e-mail [EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

Re: Newbie group/count query question

2001-05-01 Thread Thalis A. Kalfigopoulos 

On Tue, 1 May 2001, Graham Nichols wrote:

 I have a table which contains a date column and an order_number column. I
 need to formulate a query syntax to return the total number of orders for
 each day in a given month (if any). Can someone help me with the syntax
 please as I've been stumbling around with it all day without success.
 
 Many thanks,   Graham

select data_column,count(*) as number_of_orders 
from my_table 
where MONTH(date_column)=#
group by TO_DAYS(date_column);

and you replace the # with the month number you are looking for

regards,
thalis


-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, e-mail [EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php