date:20100512

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

[ANN] PBXT 1.0.11 Pre-GA Released!

2010-05-12 Thread Paul McCullagh

Hi All,

I have just released PBXT 1.0.11, which I have titled Pre-GA. Going
by our internal tests, and all instances of PBXT in production and
testing by the community this is a GA version!

However, although PBXT has 1000's of instances in production, it is
not used in very diverse applications. So I am waiting for wider
testing and usage before removing the Pre prefix.

You can download the source code from http://primebase.org, or pull it
straight from https://launchpad.net/pbxt. Here are instructions how to
compile and build the engine with MySQL: http://primebase.org/download/#qg_source
. PBXT builds with MySQL 5.1.46 GA (http://primebase.org/download/mysql-5.1.46.tar.gz
), and earlier 5.1 versions.

If you don't want to compile it yourself, PBXT 1.0.11 will soon be
available in the 5.1.46 release of MariaDB: http://askmonty.org/wiki/MariaDB:Download
. And, for the more adventurous, PBXT 1.1 is included in Drizzle: https://launchpad.net/drizzle
.

A complete list of all the changes in this version are in the release
notes: http://primebase.org/download/ChangeLog.

If you are testing PBXT and have any questions send me an e-mail. I
will be glad to help.

Otherwise, if you are looking for development or production support
for MySQL/MariaDB and PBXT then please write to: support-at-primebase-
dot-org. We are working together with Percona (http://www.percona.com)
and Monty Program Ab (http://askmonty.org/wiki/Main_Page) to provide
the service level you require.

Best regards,

Paul

--
Paul McCullagh
PrimeBase Technologies
www.primebase.org
www.blobstreaming.org
pbxt.blogspot.com

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

Count Query question

Re: Count Query question

Re: Count Query question

[ANN] PBXT 1.0.11 Pre-GA Released!

Re: Count Query question

Re: Count Query question

Re: Count Query question

Re: Count Query question

8 matches

Site Navigation

Mail list logo

Footer information