RE: Count Query question

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

Re: Count Query question

[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`)
> 

Re: Count Query question

[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_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=arch...@jab.org

Re: Count Query question

[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_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=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

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

[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=arch...@jab.org

Re: Count Query question

[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=arch...@jab.org

Re: Count Query question

[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

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

[David Arroyo Menendez]

Thanks!

2010/2/16 Peter Brawley 

>  David,
>
> >I need count the messages don'tread in a thread.
>
> Have a look at the edge list examples at
> http://www.artfulsoftware.com/mysqlbook/sampler/mysqled1ch20.html.
>
> PB
>
> -
>
> David Arroyo Menendez wrote:
>
> Hello,
>
> I've the next table structure:
>
> CREATE TABLE tx_cc20_mensajes (
> uid int(11) NOT NULL auto_increment,
> pid int(11) DEFAULT '0' NOT NULL,
> tstamp int(11) DEFAULT '0' NOT NULL,
> crdate int(11) DEFAULT '0' NOT NULL,
> cruser_id int(11) DEFAULT '0' NOT NULL,
> deleted tinyint(4) DEFAULT '0' NOT NULL,
> hidden tinyint(4) DEFAULT '0' NOT NULL,
> remitente int(11) DEFAULT '0' NOT NULL,
> destinatario int(11) DEFAULT '0' NOT NULL,
> padre int(11) DEFAULT '0' NOT NULL,
> mensaje text,
> leido tinyint(3) DEFAULT '0' NOT NULL,
>
> PRIMARY KEY (uid),
> KEY parent (pid)
> );
>
> Where padre is the id of the parent message. I need count the messages don't
> read in a thread. How can I do it?
>
> With
> $query="select count(*) as num from tx_cc20_mensajes msj where hidden=0 and
> deleted=0 and leido=0 and destinatario=".$uid." and remitente<>".$uid." and
> (padre=".$est_row['uid']." or uid=".$est_row['uid'].")";
> I am counting only the first level, but I need count the rest of children
> messages. What is the query?
>
> Thanks!
>
>
>
> --
>
>
> No virus found in this incoming message.
> Checked by AVG - www.avg.com
> Version: 8.5.435 / Virus Database: 271.1.1/2691 - Release Date: 02/16/10 
> 07:35:00
>
>
>
>

Re: count children nodes

[Peter Brawley]

David,


I need count the messages don'tread in a thread.


Have a look at the edge list examples at 
http://www.artfulsoftware.com/mysqlbook/sampler/mysqled1ch20.html.


PB

-

David Arroyo Menendez wrote:

Hello,

I've the next table structure:

CREATE TABLE tx_cc20_mensajes (
uid int(11) NOT NULL auto_increment,
pid int(11) DEFAULT '0' NOT NULL,
tstamp int(11) DEFAULT '0' NOT NULL,
crdate int(11) DEFAULT '0' NOT NULL,
cruser_id int(11) DEFAULT '0' NOT NULL,
deleted tinyint(4) DEFAULT '0' NOT NULL,
hidden tinyint(4) DEFAULT '0' NOT NULL,
remitente int(11) DEFAULT '0' NOT NULL,
destinatario int(11) DEFAULT '0' NOT NULL,
padre int(11) DEFAULT '0' NOT NULL,
mensaje text,
leido tinyint(3) DEFAULT '0' NOT NULL,

PRIMARY KEY (uid),
KEY parent (pid)
);

Where padre is the id of the parent message. I need count the messages don't
read in a thread. How can I do it?

With
$query="select count(*) as num from tx_cc20_mensajes msj where hidden=0 and
deleted=0 and leido=0 and destinatario=".$uid." and remitente<>".$uid." and
(padre=".$est_row['uid']." or uid=".$est_row['uid'].")";
I am counting only the first level, but I need count the rest of children
messages. What is the query?

Thanks!

  




No virus found in this incoming message.
Checked by AVG - www.avg.com 
Version: 8.5.435 / Virus Database: 271.1.1/2691 - Release Date: 02/16/10 07:35:00


  

RE: count children nodes

[Gavin Towey]

Trees can be complex in SQL; these articles will give some different ideas to 
handle it:
http://hashmysql.org/index.php?title=Trees_and_hierarchical_data_in_SQL
http://dev.mysql.com/tech-resources/articles/hierarchical-data.htm

Regards,
Gavin Towey

-Original Message-
From: David Arroyo Menendez [mailto:david.arr...@bbvaglobalnet.com]
Sent: Tuesday, February 16, 2010 8:27 AM
To: mysql@lists.mysql.com
Subject: count children nodes

Hello,

I've the next table structure:

CREATE TABLE tx_cc20_mensajes (
uid int(11) NOT NULL auto_increment,
pid int(11) DEFAULT '0' NOT NULL,
tstamp int(11) DEFAULT '0' NOT NULL,
crdate int(11) DEFAULT '0' NOT NULL,
cruser_id int(11) DEFAULT '0' NOT NULL,
deleted tinyint(4) DEFAULT '0' NOT NULL,
hidden tinyint(4) DEFAULT '0' NOT NULL,
remitente int(11) DEFAULT '0' NOT NULL,
destinatario int(11) DEFAULT '0' NOT NULL,
padre int(11) DEFAULT '0' NOT NULL,
mensaje text,
leido tinyint(3) DEFAULT '0' NOT NULL,

PRIMARY KEY (uid),
KEY parent (pid)
);

Where padre is the id of the parent message. I need count the messages don't
read in a thread. How can I do it?

With
$query="select count(*) as num from tx_cc20_mensajes msj where hidden=0 and
deleted=0 and leido=0 and destinatario=".$uid." and remitente<>".$uid." and
(padre=".$est_row['uid']." or uid=".$est_row['uid'].")";
I am counting only the first level, but I need count the rest of children
messages. What is the query?

Thanks!

This message contains confidential information and is intended only for the 
individual named.  If you are not the named addressee, you are notified that 
reviewing, disseminating, disclosing, copying or distributing this e-mail is 
strictly prohibited.  Please notify the sender immediately by e-mail if you 
have received this e-mail by mistake and delete this e-mail from your system. 
E-mail transmission cannot be guaranteed to be secure or error-free as 
information could be intercepted, corrupted, lost, destroyed, arrive late or 
incomplete, or contain viruses. The sender therefore does not accept liability 
for any loss or damage caused by viruses or errors or omissions in the contents 
of this message, which arise as a result of e-mail transmission. [FriendFinder 
Networks, Inc., 220 Humbolt court, Sunnyvale, CA 94089, USA, FriendFinder.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 records in join

[Miguel Vaz]

Yes, that would do what you mentioned, show all programs with a count on
events, but i need the opposite, show (and delete) all that dont have any
events. Well, just have to use IS NULL instead. Thanks.

MV


On Wed, Dec 16, 2009 at 3:17 PM, Jerry Schwartz
wrote:

> >-Original Message-
> >From: Miguel Vaz [mailto:pagong...@gmail.com]
> >Sent: Wednesday, December 16, 2009 9:39 AM
> >To: Johan De Meersman
> >Cc: Gavin Towey; mysql@lists.mysql.com
> >Subject: Re: Count records in join
> >
> >Thanks all for the feedback. Here's what i did:
> >
> >select p.id_prog,count(r.id_event) e from programas p left join(events r)
> >on(p.id_prog=r.id_prog) group by r.id_event
> >
> [JS] Add
>
> HAVING COUNT(*) > 0
>
> is one way.
>
> I haven't been following the thread, but would
>
> =
>
> SELECT `p`.`id_prod`, COUNT(`r`.`id_event`) `e` FROM
> `programas` `p` LEFT JOIN `events` r
> ON `p`.`id_prod` = `r`.`id_prod`
> WHERE `r`.`id_prod` IS NOT NULL
> GROUP BY `p`.`id_prod`;
>
> =
>
> do what you want? That should find only those rows in `programmas` that
> match
> rows in `events`, and give you the number of events for each one.
>
> Regards,
>
> Jerry Schwartz
> The Infoshop by Global Information Incorporated
> 195 Farmington Ave.
> Farmington, CT 06032
>
> 860.674.8796 / FAX: 860.674.8341
>
> www.the-infoshop.com
>
>
>
> >This gives me a list of all the distinct progs with a count of how many
> >events on each. I then delete the empty ones.
> >
> >It would be nice to be able to delete the empty ones on the same query.
> >
> >
> >MV
> >
> >
> >
> >On Wed, Dec 16, 2009 at 1:48 PM, Johan De Meersman  >wrote:
> >
> >> If the aim is purely to find the progs without events, it might be more
> >> efficient to use something like
> >>
> >> select * from progs where not exist (select id_prog from events where
> >> id_prog = progs.id_prog);
> >>
> >> My syntax might be off, check "not exists" documentation for more info.
> >>
> >>
> >> On Tue, Dec 15, 2009 at 8:14 PM, Gavin Towey  wrote:
> >>
> >>> Hi Miguel,
> >>>
> >>> You'll need to use LEFT JOIN, that will show all records that match and
> a
> >>> row in the second table will all values NULL where there is no match.
> >>> Then
> >>> you find all those rows that have no match in your WHERE clause.
> >>>
> >>> Regards,
> >>> Gavin Towey
> >>>
> >>> -Original Message-
> >>> From: Miguel Vaz [mailto:pagong...@gmail.com]
> >>> Sent: Tuesday, December 15, 2009 10:43 AM
> >>> To: mysql@lists.mysql.com
> >>> Subject: Count records in join
> >>>
> >>> Hi,
> >>>
> >>> I am stuck with a suposedly simple query:
> >>>
> >>> - i have two tables (:
> >>>
> >>> PROGS
> >>> id_prog
> >>> name
> >>>
> >>> EVENTS
> >>> id
> >>> id_prog
> >>> name
> >>>
> >>> How can i list all records from PROGS with a sum of how many events
> each
> >>> have? I want to find the progs that are empty.
> >>>
> >>> I remember something about using NULL, but i cant remember. :-P
> >>>
> >>> Thanks.
> >>>
> >>> MV
> >>>
> >>> This message contains confidential information and is intended only for
> >>> the individual named.  If you are not the named addressee, you are
> >>> notified
> >>> that reviewing, disseminating, disclosing, copying or distributing this
> >>> e-mail is strictly prohibited.  Please notify the sender immediately by
> >>> e-mail if you have received this e-mail by mistake and delete this
> e-mail
> >>> from your system. E-mail transmission cannot be guaranteed to be secure
> or
> >>> error-free as information could be intercepted, corrupted, lost,
> >>> destroyed,
> >>> arrive late or incomplete, or contain viruses. The sender therefore
> does
> >>> not
> >>> accept liability for any loss or damage caused by viruses or errors or
> >>> omissions in the contents of this message, which arise as a result of
> >>> e-mail
> >>> transmission. [FriendFinder Networks, Inc., 220 Humbolt court,
> Sunnyvale,
> >>> CA
> >>> 94089, USA, FriendFinder.com
> >>>
> >>> --
> >>> MySQL General Mailing List
> >>> For list archives: http://lists.mysql.com/mysql
> >>> To unsubscribe:
> http://lists.mysql.com/mysql?unsub=vegiv...@tuxera.be
> >>>
> >>>
> >>
>
>
>
>

RE: Count records in join

[Jerry Schwartz]

>-Original Message-
>From: Miguel Vaz [mailto:pagong...@gmail.com]
>Sent: Wednesday, December 16, 2009 9:39 AM
>To: Johan De Meersman
>Cc: Gavin Towey; mysql@lists.mysql.com
>Subject: Re: Count records in join
>
>Thanks all for the feedback. Here's what i did:
>
>select p.id_prog,count(r.id_event) e from programas p left join(events r)
>on(p.id_prog=r.id_prog) group by r.id_event
>
[JS] Add

HAVING COUNT(*) > 0

is one way.

I haven't been following the thread, but would

=

SELECT `p`.`id_prod`, COUNT(`r`.`id_event`) `e` FROM
`programas` `p` LEFT JOIN `events` r
ON `p`.`id_prod` = `r`.`id_prod`
WHERE `r`.`id_prod` IS NOT NULL
GROUP BY `p`.`id_prod`;

=

do what you want? That should find only those rows in `programmas` that match 
rows in `events`, and give you the number of events for each one.

Regards,

Jerry Schwartz
The Infoshop by Global Information Incorporated
195 Farmington Ave.
Farmington, CT 06032

860.674.8796 / FAX: 860.674.8341

www.the-infoshop.com



>This gives me a list of all the distinct progs with a count of how many
>events on each. I then delete the empty ones.
>
>It would be nice to be able to delete the empty ones on the same query.
>
>
>MV
>
>
>
>On Wed, Dec 16, 2009 at 1:48 PM, Johan De Meersman wrote:
>
>> If the aim is purely to find the progs without events, it might be more
>> efficient to use something like
>>
>> select * from progs where not exist (select id_prog from events where
>> id_prog = progs.id_prog);
>>
>> My syntax might be off, check "not exists" documentation for more info.
>>
>>
>> On Tue, Dec 15, 2009 at 8:14 PM, Gavin Towey  wrote:
>>
>>> Hi Miguel,
>>>
>>> You'll need to use LEFT JOIN, that will show all records that match and a
>>> row in the second table will all values NULL where there is no match. 
>>> Then
>>> you find all those rows that have no match in your WHERE clause.
>>>
>>> Regards,
>>> Gavin Towey
>>>
>>> -Original Message-
>>> From: Miguel Vaz [mailto:pagong...@gmail.com]
>>> Sent: Tuesday, December 15, 2009 10:43 AM
>>> To: mysql@lists.mysql.com
>>> Subject: Count records in join
>>>
>>> Hi,
>>>
>>> I am stuck with a suposedly simple query:
>>>
>>> - i have two tables (:
>>>
>>> PROGS
>>> id_prog
>>> name
>>>
>>> EVENTS
>>> id
>>> id_prog
>>> name
>>>
>>> How can i list all records from PROGS with a sum of how many events each
>>> have? I want to find the progs that are empty.
>>>
>>> I remember something about using NULL, but i cant remember. :-P
>>>
>>> Thanks.
>>>
>>> MV
>>>
>>> This message contains confidential information and is intended only for
>>> the individual named.  If you are not the named addressee, you are 
>>> notified
>>> that reviewing, disseminating, disclosing, copying or distributing this
>>> e-mail is strictly prohibited.  Please notify the sender immediately by
>>> e-mail if you have received this e-mail by mistake and delete this e-mail
>>> from your system. E-mail transmission cannot be guaranteed to be secure or
>>> error-free as information could be intercepted, corrupted, lost, 
>>> destroyed,
>>> arrive late or incomplete, or contain viruses. The sender therefore does 
>>> not
>>> accept liability for any loss or damage caused by viruses or errors or
>>> omissions in the contents of this message, which arise as a result of 
>>> e-mail
>>> transmission. [FriendFinder Networks, Inc., 220 Humbolt court, Sunnyvale, 
>>> CA
>>> 94089, USA, FriendFinder.com
>>>
>>> --
>>> MySQL General Mailing List
>>> For list archives: http://lists.mysql.com/mysql
>>> To unsubscribe:http://lists.mysql.com/mysql?unsub=vegiv...@tuxera.be
>>>
>>>
>>




-- 
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 records in join

[Miguel Vaz]

Thanks all for the feedback. Here's what i did:

select p.id_prog,count(r.id_event) e from programas p left join(events r)
on(p.id_prog=r.id_prog) group by r.id_event

This gives me a list of all the distinct progs with a count of how many
events on each. I then delete the empty ones.

It would be nice to be able to delete the empty ones on the same query.


MV



On Wed, Dec 16, 2009 at 1:48 PM, Johan De Meersman wrote:

> If the aim is purely to find the progs without events, it might be more
> efficient to use something like
>
> select * from progs where not exist (select id_prog from events where
> id_prog = progs.id_prog);
>
> My syntax might be off, check "not exists" documentation for more info.
>
>
> On Tue, Dec 15, 2009 at 8:14 PM, Gavin Towey  wrote:
>
>> Hi Miguel,
>>
>> You'll need to use LEFT JOIN, that will show all records that match and a
>> row in the second table will all values NULL where there is no match.  Then
>> you find all those rows that have no match in your WHERE clause.
>>
>> Regards,
>> Gavin Towey
>>
>> -Original Message-
>> From: Miguel Vaz [mailto:pagong...@gmail.com]
>> Sent: Tuesday, December 15, 2009 10:43 AM
>> To: mysql@lists.mysql.com
>> Subject: Count records in join
>>
>> Hi,
>>
>> I am stuck with a suposedly simple query:
>>
>> - i have two tables (:
>>
>> PROGS
>> id_prog
>> name
>>
>> EVENTS
>> id
>> id_prog
>> name
>>
>> How can i list all records from PROGS with a sum of how many events each
>> have? I want to find the progs that are empty.
>>
>> I remember something about using NULL, but i cant remember. :-P
>>
>> Thanks.
>>
>> MV
>>
>> This message contains confidential information and is intended only for
>> the individual named.  If you are not the named addressee, you are notified
>> that reviewing, disseminating, disclosing, copying or distributing this
>> e-mail is strictly prohibited.  Please notify the sender immediately by
>> e-mail if you have received this e-mail by mistake and delete this e-mail
>> from your system. E-mail transmission cannot be guaranteed to be secure or
>> error-free as information could be intercepted, corrupted, lost, destroyed,
>> arrive late or incomplete, or contain viruses. The sender therefore does not
>> accept liability for any loss or damage caused by viruses or errors or
>> omissions in the contents of this message, which arise as a result of e-mail
>> transmission. [FriendFinder Networks, Inc., 220 Humbolt court, Sunnyvale, CA
>> 94089, USA, FriendFinder.com
>>
>> --
>> MySQL General Mailing List
>> For list archives: http://lists.mysql.com/mysql
>> To unsubscribe:http://lists.mysql.com/mysql?unsub=vegiv...@tuxera.be
>>
>>
>

Re: Count records in join

[Johan De Meersman]

If the aim is purely to find the progs without events, it might be more
efficient to use something like

select * from progs where not exist (select id_prog from events where
id_prog = progs.id_prog);

My syntax might be off, check "not exists" documentation for more info.


On Tue, Dec 15, 2009 at 8:14 PM, Gavin Towey  wrote:

> Hi Miguel,
>
> You'll need to use LEFT JOIN, that will show all records that match and a
> row in the second table will all values NULL where there is no match.  Then
> you find all those rows that have no match in your WHERE clause.
>
> Regards,
> Gavin Towey
>
> -Original Message-
> From: Miguel Vaz [mailto:pagong...@gmail.com]
> Sent: Tuesday, December 15, 2009 10:43 AM
> To: mysql@lists.mysql.com
> Subject: Count records in join
>
> Hi,
>
> I am stuck with a suposedly simple query:
>
> - i have two tables (:
>
> PROGS
> id_prog
> name
>
> EVENTS
> id
> id_prog
> name
>
> How can i list all records from PROGS with a sum of how many events each
> have? I want to find the progs that are empty.
>
> I remember something about using NULL, but i cant remember. :-P
>
> Thanks.
>
> MV
>
> This message contains confidential information and is intended only for the
> individual named.  If you are not the named addressee, you are notified that
> reviewing, disseminating, disclosing, copying or distributing this e-mail is
> strictly prohibited.  Please notify the sender immediately by e-mail if you
> have received this e-mail by mistake and delete this e-mail from your
> system. E-mail transmission cannot be guaranteed to be secure or error-free
> as information could be intercepted, corrupted, lost, destroyed, arrive late
> or incomplete, or contain viruses. The sender therefore does not accept
> liability for any loss or damage caused by viruses or errors or omissions in
> the contents of this message, which arise as a result of e-mail
> transmission. [FriendFinder Networks, Inc., 220 Humbolt court, Sunnyvale, CA
> 94089, USA, FriendFinder.com
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe:http://lists.mysql.com/mysql?unsub=vegiv...@tuxera.be
>
>

RE: Count records in join

[Gavin Towey]

Hi Miguel,

You'll need to use LEFT JOIN, that will show all records that match and a row 
in the second table will all values NULL where there is no match.  Then you 
find all those rows that have no match in your WHERE clause.

Regards,
Gavin Towey

-Original Message-
From: Miguel Vaz [mailto:pagong...@gmail.com]
Sent: Tuesday, December 15, 2009 10:43 AM
To: mysql@lists.mysql.com
Subject: Count records in join

Hi,

I am stuck with a suposedly simple query:

- i have two tables (:

PROGS
id_prog
name

EVENTS
id
id_prog
name

How can i list all records from PROGS with a sum of how many events each
have? I want to find the progs that are empty.

I remember something about using NULL, but i cant remember. :-P

Thanks.

MV

This message contains confidential information and is intended only for the 
individual named.  If you are not the named addressee, you are notified that 
reviewing, disseminating, disclosing, copying or distributing this e-mail is 
strictly prohibited.  Please notify the sender immediately by e-mail if you 
have received this e-mail by mistake and delete this e-mail from your system. 
E-mail transmission cannot be guaranteed to be secure or error-free as 
information could be intercepted, corrupted, lost, destroyed, arrive late or 
incomplete, or contain viruses. The sender therefore does not accept liability 
for any loss or damage caused by viruses or errors or omissions in the contents 
of this message, which arise as a result of e-mail transmission. [FriendFinder 
Networks, Inc., 220 Humbolt court, Sunnyvale, CA 94089, USA, FriendFinder.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 from 2 tables

[b]

On 07/08/2009 06:11 PM, Gary Smith wrote:

Create a view or sub select, denormalizing the data and then group it.

select month, sum(login) as num_logins, sum(download) as num_downloads
from
(
select
monthname(s.created) as month_name
, if(ifnull(s.id, 0)>  0, 1, 0) as login
, if(ifnull(d.id, 0)>  0, 1, 0) as download
from sessions s left join downloads d
on s.id = d.session_id
)
group by month name;

which is the left table?  downloads or logins?  If logins shouldn't it be on 
the left side of the ON statement? (I'm not sure)  My understanding is that is 
the ON statement that runs the left join, not which table is listed first (I 
could be wrong though -- chime in if you know the definitive answer please).

Anyway, try this and see if it gets you closer.


I had to change month_name to month and add "AS foo" just before the 
GROUP BY ("Every derived table must have its own alias") but still no 
joy. I'm still getting those same incorrect numbers.


--
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 from 2 tables

[Gary Smith]

Create a view or sub select, denormalizing the data and then group it.

select month, sum(login) as num_logins, sum(download) as num_downloads
from
(
select 
monthname(s.created) as month_name
, if(ifnull(s.id, 0) > 0, 1, 0) as login
, if(ifnull(d.id, 0) > 0, 1, 0) as download
from sessions s left join downloads d
on s.id = d.session_id
)
group by month name;

which is the left table?  downloads or logins?  If logins shouldn't it be on 
the left side of the ON statement? (I'm not sure)  My understanding is that is 
the ON statement that runs the left join, not which table is listed first (I 
could be wrong though -- chime in if you know the definitive answer please).

Anyway, try this and see if it gets you closer.

From: b [my...@logi.ca]
Sent: Wednesday, July 08, 2009 12:55 PM
To: mysql@lists.mysql.com
Subject: Re: COUNT from 2 tables

On 07/08/2009 03:33 PM, Gary Smith wrote:
> Off the top of my head, try this.
>
> SELECT
> MONTHNAME(s.created) AS month,
> sum(if(ifnull(s.id,0)>  0, 1, 0)) AS num_logins,
> sim(if(ifnull(d.id, 0)>  0, 1, 0)) AS num_downloads
> FROM sessions AS s LEFT JOIN downloads AS d
> ON d.session_id = s.id GROUP BY month
>

Nope, I'm still getting those same incorrect sums. Thanks, though. It
seems to me that the problem is that I'm grouping by the month for one
table but counting from both.

I'd paste the output here but I just upgraded Fedora and the BETA (wtf?)
version of Thunderbird crashes when I paste into an email (how the
earlier paste worked I don't know).

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=g...@primeexalia.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 from 2 tables

[b]

On 07/08/2009 03:33 PM, Gary Smith wrote:

Off the top of my head, try this.

SELECT
MONTHNAME(s.created) AS month,
sum(if(ifnull(s.id,0)>  0, 1, 0)) AS num_logins,
sim(if(ifnull(d.id, 0)>  0, 1, 0)) AS num_downloads
FROM sessions AS s LEFT JOIN downloads AS d
ON d.session_id = s.id GROUP BY month



Nope, I'm still getting those same incorrect sums. Thanks, though. It 
seems to me that the problem is that I'm grouping by the month for one 
table but counting from both.


I'd paste the output here but I just upgraded Fedora and the BETA (wtf?) 
version of Thunderbird crashes when I paste into an email (how the 
earlier paste worked I don't know).


--
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 from 2 tables

[Gary Smith]

Off the top of my head, try this.

SELECT 
MONTHNAME(s.created) AS month, 
sum(if(ifnull(s.id,0) > 0, 1, 0)) AS num_logins,
sim(if(ifnull(d.id, 0)> 0, 1, 0)) AS num_downloads 
FROM sessions AS s LEFT JOIN downloads AS d
ON d.session_id = s.id GROUP BY month


From: b [my...@logi.ca]
Sent: Wednesday, July 08, 2009 12:21 PM
To: mysql@lists.mysql.com
Subject: COUNT from 2 tables

I'm trying to get a count for columns in 2 tables at once. I have
sessions and downloads tables and would like to get some basic stats.

mysql> describe sessions;
+-+--+--+-+-++
| Field   | Type | Null | Key | Default | Extra  |
+-+--+--+-+-++
| id  | int(10) unsigned | NO   | PRI | NULL| auto_increment |
| created | datetime | YES  | | NULL||
| user_id | int(10) unsigned | NO   | MUL | NULL||
+-+--+--+-+-++
3 rows in set (0.01 sec)

mysql> describe downloads;
+--+--+--+-+-++
| Field| Type | Null | Key | Default | Extra  |
+--+--+--+-+-++
| id   | int(10) unsigned | NO   | PRI | NULL| auto_increment |
| created  | datetime | YES  | | NULL||
| user_id  | int(10) unsigned | NO   | MUL | NULL||
| item_file_id | int(10) unsigned | NO   | MUL | NULL||
| session_id | int(10) unsigned | NO   | | NULL|
 |
| path | text | NO   | | NULL||
+--+--+--+-+-++
6 rows in set (0.01 sec)

mysql> SELECT MONTHNAME(created) AS month, COUNT(*) AS num_logins FROM
sessions GROUP BY month;
+---++
| month | num_logins |
+---++
| July  |  6 |
| June  |214 |
| May   |150 |
+---++
3 rows in set (0.00 sec)

mysql> SELECT MONTHNAME(created) AS month, COUNT(*) AS num_downloads
FROM downloads GROUP BY month;
+---+---+
| month | num_downloads |
+---+---+
| June  |   389 |
| May   |   220 |
+---+---+
2 rows in set (0.01 sec)


In trying to get the count from both tables at once, the logins are no
longer being summed as expected:

mysql> SELECT MONTHNAME(s.created) AS month, COUNT(s.id) AS num_logins,
COUNT(d.id) AS num_downloads FROM sessions AS s LEFT JOIN downloads AS d
ON d.session_id = s.id GROUP BY month;
+---++---+
| month | num_logins | num_downloads |
+---++---+
| July  |  6 | 0 |
| June  |539 |   389 |
| May   |350 |   220 |
+---++---+
3 rows in set (0.31 sec)


Is this possible to do without using a sub-query?

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=g...@primeexalia.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 returned rows of a SELECT

[Fish Kungfu]

Beautiful!  That's exactly what I needed.  Thanks, Roy

mysql> SELECT COUNT(*) FROM (SELECT aviName,MAX(dateTime) FROM aviTrackerMain 
WHERE DATE(dateTime) LIKE CONCAT(DATE(NOW()),'%') GROUP BY aviName) as T;
+--+
| COUNT(*) |
+--+
|   49 | 
+--+
1 row in set (0.30 sec)


CheersFish


-Original Message-
>From: Roy Lyseng <[EMAIL PROTECTED]>
>Sent: Jul 31, 2008 9:41 AM
>To: Fish Kungfu <[EMAIL PROTECTED]>
>Subject: Re: COUNT returned rows of a SELECT
>
>Hi,
>
>generally you should be able to use the select query as a derived table, 
>and select the row count from this:
>
>select count(*) from (select ) as T;
>
>Note that the derived table always needs an alias (here T).
>
>Cheers,
>Roy
>
>Fish Kungfu wrote:
>> Thanks for trying guys, but that's still not quite what I'm looking
>> for.  All I really want is the total number of rows returned for the
>> query result.
>> 
>> For example, my the SELECT that Ananda suggested returns this:
>> 
>> mysql> SELECT aviName,MAX(dateTime) ,count(*) FROM aviTrackerMain WHERE
>> DATE(dateTime) LIKE CONCAT(DATE(NOW()),'%') GROUP BY aviName;
>> +---+-+--+
>> | aviName   | MAX(dateTime)   | count(*) |
>> +---+-+--+
>> | user1 | 2008-07-31 02:28:42 |6 |
>> | user2 | 2008-07-31 04:56:43 |4 |
>> | user3 | 2008-07-31 06:54:44 |2 |
>> | user4 | 2008-07-31 03:10:43 |1 |
>> | user5 | 2008-07-31 07:02:44 |   67 |
>> | user6 | 2008-07-31 00:42:42 |1 |
>> | user7 | 2008-07-31 01:02:42 |   10 |
>> | user8 | 2008-07-31 00:22:41 |   22 |
>> | user9 | 2008-07-31 00:22:42 |   22 |
>> | user10| 2008-07-31 05:16:44 |   16 |
>> | user11| 2008-07-31 05:15:44 |1 |
>> +---+-+--+
>> 11 rows in set (0.11 sec)
>> 
>> What I'd like to have returned is, "11", the final total number of rows
>> that the query returned, not the count of each user occurance per GROUPing.
>> 
>> Ideally, I was hoping COUNT() could work like this, BUT it doesn't of
>> course:
>> 
>> mysql> SELECT COUNT(SELECT aviName,MAX(dateTime) ,count(*) FROM
>> aviTrackerMain WHERE DATE(dateTime) LIKE CONCAT(DATE(NOW()),'%') GROUP
>> BY aviName);
>> +--+
>> | COUNT(SELECT aviName,MAX(dateTime) ,count(*) FROM aviTrackerMain WHERE
>> DATE(dateTime) LIKE CONCAT(DATE(NOW()),'%') GROUP BY aviName) |
>> +--+
>> |
>>
>> 11|
>> +--+
>> 
>> 
>> 
>> 
>> Rob Wultsch wrote:
>>> On Wed, Jul 30, 2008 at 9:41 PM, Fish Kungfu <[EMAIL PROTECTED]> wrote:
>>>> Using MySQL commands only (not PHP's mysql_num_rows), is there a way to
>>>> COUNT the number of rows returned from a SELECT.GROUP BY?
>>>>
>>>> My primary SELECT query is this:
>>>>
>>>> SELECT aviName,MAX(dateTime) FROM aviTrackerMain WHERE DATE(dateTime)
>>>> LIKE CONCAT(DATE(NOW()),'%') GROUP BY aviName;
>>>>
>>>> And it faithfully returns x-number of rows.  However, I want to be able
>>>> to capture the number of rows it returns.  If I have to I will use PHP,
>>>> but I was hoping for a way to do it with just MySQL.
>>>>
>>>>
>>>> Thanks very much in advance..Fish
>>> FOUND_ROWS() might be a solution that works for you. Take a look at:
>>> http://dev.mysql.com/doc/refman/5.0/en/information-functions.html#function_found-rows
>>>
>> 
>> 


-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: COUNT returned rows of a SELECT

[Perrin Harkins]

On Thu, Jul 31, 2008 at 8:00 AM, Fish Kungfu <[EMAIL PROTECTED]> wrote:
> Ideally, I was hoping COUNT() could work like this, BUT it doesn't of
> course:
>
> mysql> SELECT COUNT(SELECT aviName,MAX(dateTime) ,count(*) FROM
> aviTrackerMain WHERE DATE(dateTime) LIKE CONCAT(DATE(NOW()),'%') GROUP
> BY aviName);

SELECT COUNT(DISTINCT aviName);

I don't really understand why you don't want to just look at the
number of rows you get from the first query though.

- Perrin

-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

RE: COUNT returned rows of a SELECT

[Jerry Schwartz]

>-Original Message-
>From: Fish Kungfu [mailto:[EMAIL PROTECTED]
>Sent: Thursday, July 31, 2008 12:41 AM
>To: mysql@lists.mysql.com
>Subject: COUNT returned rows of a SELECT
>
>Using MySQL commands only (not PHP's mysql_num_rows), is there a way to
>COUNT the number of rows returned from a SELECT.GROUP BY?
>
>My primary SELECT query is this:
>
>SELECT aviName,MAX(dateTime) FROM aviTrackerMain WHERE DATE(dateTime)
>LIKE CONCAT(DATE(NOW()),'%') GROUP BY aviName;
>
[JS]

SELECT SQL_CALC_FOUND_ROWS aviName,MAX(dateTime) FROM aviTrackerMain WHERE
DATE(dateTime)
LIKE CONCAT(DATE(NOW()),'%') GROUP BY aviName;
SELECT FOUND_ROWS();

>And it faithfully returns x-number of rows.  However, I want to be able
>to capture the number of rows it returns.  If I have to I will use PHP,
>but I was hoping for a way to do it with just MySQL.
>
>
>Thanks very much in advance..Fish
>
>
>--
>MySQL General Mailing List
>For list archives: http://lists.mysql.com/mysql
>To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
>infoshop.com





-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: COUNT returned rows of a SELECT

[Fish Kungfu]

Thanks for trying guys, but that's still not quite what I'm looking
for.  All I really want is the total number of rows returned for the
query result.

For example, my the SELECT that Ananda suggested returns this:

mysql> SELECT aviName,MAX(dateTime) ,count(*) FROM aviTrackerMain WHERE
DATE(dateTime) LIKE CONCAT(DATE(NOW()),'%') GROUP BY aviName;
+---+-+--+
| aviName   | MAX(dateTime)   | count(*) |
+---+-+--+
| user1 | 2008-07-31 02:28:42 |6 |
| user2 | 2008-07-31 04:56:43 |4 |
| user3 | 2008-07-31 06:54:44 |2 |
| user4 | 2008-07-31 03:10:43 |1 |
| user5 | 2008-07-31 07:02:44 |   67 |
| user6 | 2008-07-31 00:42:42 |1 |
| user7 | 2008-07-31 01:02:42 |   10 |
| user8 | 2008-07-31 00:22:41 |   22 |
| user9 | 2008-07-31 00:22:42 |   22 |
| user10| 2008-07-31 05:16:44 |   16 |
| user11| 2008-07-31 05:15:44 |1 |
+---+-+--+
11 rows in set (0.11 sec)

What I'd like to have returned is, "11", the final total number of rows
that the query returned, not the count of each user occurance per GROUPing.

Ideally, I was hoping COUNT() could work like this, BUT it doesn't of
course:

mysql> SELECT COUNT(SELECT aviName,MAX(dateTime) ,count(*) FROM
aviTrackerMain WHERE DATE(dateTime) LIKE CONCAT(DATE(NOW()),'%') GROUP
BY aviName);
+--+
| COUNT(SELECT aviName,MAX(dateTime) ,count(*) FROM aviTrackerMain WHERE
DATE(dateTime) LIKE CONCAT(DATE(NOW()),'%') GROUP BY aviName) |
+--+
|   

11|
+--+




Rob Wultsch wrote:
> On Wed, Jul 30, 2008 at 9:41 PM, Fish Kungfu <[EMAIL PROTECTED]> wrote:
>> Using MySQL commands only (not PHP's mysql_num_rows), is there a way to
>> COUNT the number of rows returned from a SELECT.GROUP BY?
>>
>> My primary SELECT query is this:
>>
>> SELECT aviName,MAX(dateTime) FROM aviTrackerMain WHERE DATE(dateTime)
>> LIKE CONCAT(DATE(NOW()),'%') GROUP BY aviName;
>>
>> And it faithfully returns x-number of rows.  However, I want to be able
>> to capture the number of rows it returns.  If I have to I will use PHP,
>> but I was hoping for a way to do it with just MySQL.
>>
>>
>> Thanks very much in advance..Fish
>
> FOUND_ROWS() might be a solution that works for you. Take a look at:
> http://dev.mysql.com/doc/refman/5.0/en/information-functions.html#function_found-rows
>

Re: COUNT returned rows of a SELECT

[Rob Wultsch]

On Wed, Jul 30, 2008 at 9:41 PM, Fish Kungfu <[EMAIL PROTECTED]> wrote:
> Using MySQL commands only (not PHP's mysql_num_rows), is there a way to
> COUNT the number of rows returned from a SELECT.GROUP BY?
>
> My primary SELECT query is this:
>
> SELECT aviName,MAX(dateTime) FROM aviTrackerMain WHERE DATE(dateTime)
> LIKE CONCAT(DATE(NOW()),'%') GROUP BY aviName;
>
> And it faithfully returns x-number of rows.  However, I want to be able
> to capture the number of rows it returns.  If I have to I will use PHP,
> but I was hoping for a way to do it with just MySQL.
>
>
> Thanks very much in advance..Fish

FOUND_ROWS() might be a solution that works for you. Take a look at:
http://dev.mysql.com/doc/refman/5.0/en/information-functions.html#function_found-rows

-- 
Rob Wultsch

-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: COUNT returned rows of a SELECT

[Ananda Kumar]

SELECT aviName,MAX(dateTime) ,count(*) FROM aviTrackerMain WHERE
DATE(dateTime)
LIKE CONCAT(DATE(NOW()),'%') GROUP BY aviName;


This will also give you count of rows



On 7/31/08, Fish Kungfu <[EMAIL PROTECTED]> wrote:
>
> Using MySQL commands only (not PHP's mysql_num_rows), is there a way to
> COUNT the number of rows returned from a SELECT.GROUP BY?
>
> My primary SELECT query is this:
>
> SELECT aviName,MAX(dateTime) FROM aviTrackerMain WHERE DATE(dateTime)
> LIKE CONCAT(DATE(NOW()),'%') GROUP BY aviName;
>
> And it faithfully returns x-number of rows.  However, I want to be able
> to capture the number of rows it returns.  If I have to I will use PHP,
> but I was hoping for a way to do it with just MySQL.
>
>
> Thanks very much in advance..Fish
>
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
>
>

Re: Count total number of records in db

[Rob Wultsch]

On Fri, Jul 11, 2008 at 4:24 AM, Warren Windvogel
<[EMAIL PROTECTED]> wrote:
> $tables = mysql_list_tables($DB_DBName);

Not that it matters much, but mysql_list_tables() is deprecated.

-- 
Rob Wultsch

-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: Count total number of records in db

[Warren Windvogel]

Radoulov, Dimitre wrote:

mysql -NBe'show databases' |
 while IFS= read -r db; do
   printf "show tables from %s;\n" "$db" |
 mysql -N | while IFS= read -r t; do
   printf "select count(1) from %s.%s;\n" "$db" "$t"
 done
 done | mysql -N |
   awk '{ s += $1 }END{ print s }'


I quickly put together a PHP script to do it. Its dirty( purpose built 
:-) ) but it works.


  or die("Couldn't connect to MySQL:" . mysql_error() . "" . 
mysql_errno());

//select database
$Db = @mysql_select_db($DB_DBName, $Connect)
  or die("Couldn't select database:" . mysql_error(). "" . 
mysql_errno());


$tables = mysql_list_tables($DB_DBName);
$count = 0;
$total_rows = 0;

while ($count < mysql_numrows($tables)) {
$table_name = mysql_tablename($tables,$count);
$sql = 'SELECT COUNT(*) FROM '.$table_name;
$result = mysql_query($sql);
$table_count = mysql_fetch_row($result);
$table_count = $table_count[0];
$total_rows = $total_rows + $table_count;
echo 'Number of rows in '.$table_name.' = 
'.$table_count.'';

$count++;
}
echo '';
echo 'Total number of rows in database: '.$total_rows;

?>

Thanks guys.
Warren
--
Open Source Developer
Business Data Solutions
Email: [EMAIL PROTECTED]
Gmail: wwindvogel
MSN: wwindvogel
Skype: wwindvogel
Cell: 27 73 264 6700
Tel: 27 21 487 2177

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: Count total number of records in db

[Radoulov, Dimitre]

Warren Windvogel wrote:

Hi,

Can anyone tell me how to check the total number of records in a
database in MySQL version 4.0
Googling doesn't seem to help and all previous posts assume version 5.*



[...]

Something like this:

mysql -NBe'show databases' |
 while IFS= read -r db; do
   printf "show tables from %s;\n" "$db" |
 mysql -N | while IFS= read -r t; do
   printf "select count(1) from %s.%s;\n" "$db" "$t"
 done
 done | mysql -N |
   awk '{ s += $1 }END{ print s }' 



Regards
Dimitre


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: Count total number of records in db

[MarisRuskulis]

Warren Windvogel wrote:
> Hi,
>
> Can anyone tell me how to check the total number of records in a
> database in MySQL version 4.0
> Googling doesn't seem to help and all previous posts assume version 5.*
>
> Regards
> Warren
>
for table you can use:
SELECT COUNT(*) from the_table_name;
in whole database you can iterate trough all tables, for that create
simple bash script. For example:

#!/bin/bash

tables=`mysql -uUser -pPass -hHost database_name --skip-column-names -e
"SHOW TABLES"`
total=0
for table in $tables
do
rows=`mysql -uUser -pPass -hHost database_name --skip-column-names
-e "SELECT COUNT(*) from $table"`
total=$(($total+rows))
done
echo $total


-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

RE: Count syntax

[Beauford]

Thanks to all. 

> -Original Message-
> From: Michael Dykman [mailto:[EMAIL PROTECTED] 
> Sent: September 28, 2007 1:36 PM
> To: Beauford
> Cc: mysql@lists.mysql.com
> Subject: Re: Count syntax
> 
> 1 means that 1 will be added to the sum if the condition 
> tests, otherwise 0 will be added to the sum.  the condition 
> in this case is (supportertype = 'L') and will be applied to 
> every row.
> 
> On 9/28/07, Beauford <[EMAIL PROTECTED]> wrote:
> > Thanks - it works, but what does the 1 and 0 do in this - 
> > SUM(IF(supportertype = 'L', 1, 0))
> >
> > > -Original Message-
> > > From: Baron Schwartz [mailto:[EMAIL PROTECTED]
> > > Sent: September 28, 2007 1:00 PM
> > > To: Beauford
> > > Cc: mysql@lists.mysql.com
> > > Subject: Re: Count syntax
> > >
> > > Beauford wrote:
> > > > Hi,
> > > >
> > > > I have the following line of code and I keep getting wrong
> > > results from it.
> > > > Can someone let me know what I'm doing wrong here. I just
> > > can't quite
> > > > figure out the syntax that I need.
> > > >
> > > > select count(*) as numrows, count(supportertype) as 
> leadcar from 
> > > > registrar where supportertype = 'L';
> > > >
> > > > What I want to do is count the total number of records and then 
> > > > the total number of records that have "L" as the supportertype
> > > and then display them.
> > > >
> > > > So I should have something like "There are 100 total 
> records and 
> > > > 22 with Supporter Type L".
> > >
> > > Try this:
> > >
> > > SELECT COUNT(*), SUM(IF(supportertype = 'L', 1, 0)) FROM 
> registrar;
> > >
> > > Baron
> > >
> > > --
> > > MySQL General Mailing List
> > > For list archives: http://lists.mysql.com/mysql To unsubscribe:
> > > http://lists.mysql.com/[EMAIL PROTECTED]
> > >
> > >
> > >
> >
> >
> >
> >
> > --
> > MySQL General Mailing List
> > For list archives: http://lists.mysql.com/mysql
> > To unsubscribe:
> http://lists.mysql.com/[EMAIL PROTECTED]
> >
> >
> 
> 
> -- 
>  - michael dykman
>  - [EMAIL PROTECTED]
> 
>  - All models are wrong.  Some models are useful.
> 
> -- 
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe:
> http://lists.mysql.com/[EMAIL PROTECTED]
> 
> 
> 




-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

RE: Count syntax

[Weston, Craig \(OFT\)]

Beauford, you might find this article on cross joins interesting, it was
something shown to me a few weeks ago that discusses this kind of
function.

http://dev.mysql.com/tech-resources/articles/wizard/print_version.html
Cheers,
craig



This e-mail, including any attachments, may be confidential, privileged or 
otherwise legally protected. It is intended only for the addressee. If you 
received this e-mail in error or from someone who was not authorized to send it 
to you, do not disseminate, copy or otherwise use this e-mail or its 
attachments.  Please notify the sender immediately by reply e-mail and delete 
the e-mail from your system.


-Original Message-

From: Michael Dykman [mailto:[EMAIL PROTECTED] 
Sent: Friday, September 28, 2007 1:36 PM
To: Beauford
Cc: mysql@lists.mysql.com
Subject: Re: Count syntax

1 means that 1 will be added to the sum if the condition tests,
otherwise 0 will be added to the sum.  the condition in this case is
(supportertype = 'L') and will be applied to every row.

On 9/28/07, Beauford <[EMAIL PROTECTED]> wrote:
> Thanks - it works, but what does the 1 and 0 do in this -
> SUM(IF(supportertype = 'L', 1, 0))
>
> > -Original Message-
> > From: Baron Schwartz [mailto:[EMAIL PROTECTED]
> > Sent: September 28, 2007 1:00 PM
> > To: Beauford
> > Cc: mysql@lists.mysql.com
> > Subject: Re: Count syntax
> >
> > Beauford wrote:
> > > Hi,
> > >
> > > I have the following line of code and I keep getting wrong
> > results from it.
> > > Can someone let me know what I'm doing wrong here. I just
> > can't quite
> > > figure out the syntax that I need.
> > >
> > > select count(*) as numrows, count(supportertype) as leadcar from
> > > registrar where supportertype = 'L';
> > >
> > > What I want to do is count the total number of records and then
the
> > > total number of records that have "L" as the supportertype
> > and then display them.
> > >
> > > So I should have something like "There are 100 total records and
22
> > > with Supporter Type L".
> >
> > Try this:
> >
> > SELECT COUNT(*), SUM(IF(supportertype = 'L', 1, 0)) FROM registrar;
> >
> > Baron
> >
> > --
> > MySQL General Mailing List
> > For list archives: http://lists.mysql.com/mysql
> > To unsubscribe:
> > http://lists.mysql.com/[EMAIL PROTECTED]
> >
> >
> >
>
>
>
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe:
http://lists.mysql.com/[EMAIL PROTECTED]
>
>


-- 
 - michael dykman
 - [EMAIL PROTECTED]

 - All models are wrong.  Some models are useful.

-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:
http://lists.mysql.com/[EMAIL PROTECTED]


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: Count syntax

[Michael Dykman]

1 means that 1 will be added to the sum if the condition tests,
otherwise 0 will be added to the sum.  the condition in this case is
(supportertype = 'L') and will be applied to every row.

On 9/28/07, Beauford <[EMAIL PROTECTED]> wrote:
> Thanks - it works, but what does the 1 and 0 do in this -
> SUM(IF(supportertype = 'L', 1, 0))
>
> > -Original Message-
> > From: Baron Schwartz [mailto:[EMAIL PROTECTED]
> > Sent: September 28, 2007 1:00 PM
> > To: Beauford
> > Cc: mysql@lists.mysql.com
> > Subject: Re: Count syntax
> >
> > Beauford wrote:
> > > Hi,
> > >
> > > I have the following line of code and I keep getting wrong
> > results from it.
> > > Can someone let me know what I'm doing wrong here. I just
> > can't quite
> > > figure out the syntax that I need.
> > >
> > > select count(*) as numrows, count(supportertype) as leadcar from
> > > registrar where supportertype = 'L';
> > >
> > > What I want to do is count the total number of records and then the
> > > total number of records that have "L" as the supportertype
> > and then display them.
> > >
> > > So I should have something like "There are 100 total records and 22
> > > with Supporter Type L".
> >
> > Try this:
> >
> > SELECT COUNT(*), SUM(IF(supportertype = 'L', 1, 0)) FROM registrar;
> >
> > Baron
> >
> > --
> > MySQL General Mailing List
> > For list archives: http://lists.mysql.com/mysql
> > To unsubscribe:
> > http://lists.mysql.com/[EMAIL PROTECTED]
> >
> >
> >
>
>
>
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
>
>


-- 
 - michael dykman
 - [EMAIL PROTECTED]

 - All models are wrong.  Some models are useful.

-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

RE: Count syntax

[Beauford]

Thanks - it works, but what does the 1 and 0 do in this -
SUM(IF(supportertype = 'L', 1, 0))

> -Original Message-
> From: Baron Schwartz [mailto:[EMAIL PROTECTED] 
> Sent: September 28, 2007 1:00 PM
> To: Beauford
> Cc: mysql@lists.mysql.com
> Subject: Re: Count syntax
> 
> Beauford wrote:
> > Hi,
> > 
> > I have the following line of code and I keep getting wrong 
> results from it.
> > Can someone let me know what I'm doing wrong here. I just 
> can't quite 
> > figure out the syntax that I need.
> > 
> > select count(*) as numrows, count(supportertype) as leadcar from 
> > registrar where supportertype = 'L';
> > 
> > What I want to do is count the total number of records and then the 
> > total number of records that have "L" as the supportertype 
> and then display them.
> > 
> > So I should have something like "There are 100 total records and 22 
> > with Supporter Type L".
> 
> Try this:
> 
> SELECT COUNT(*), SUM(IF(supportertype = 'L', 1, 0)) FROM registrar;
> 
> Baron
> 
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe:
> http://lists.mysql.com/[EMAIL PROTECTED]
> 
> 
> 




-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: Count syntax

[Baron Schwartz]

Beauford wrote:

Hi,

I have the following line of code and I keep getting wrong results from it.
Can someone let me know what I'm doing wrong here. I just can't quite figure
out the syntax that I need.

select count(*) as numrows, count(supportertype) as leadcar from registrar
where supportertype = 'L';

What I want to do is count the total number of records and then the total
number of records that have "L" as the supportertype and then display them.

So I should have something like "There are 100 total records and 22 with
Supporter Type L".


Try this:

SELECT COUNT(*), SUM(IF(supportertype = 'L', 1, 0)) FROM registrar;

Baron

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: Count the number of specific rows

[Philip Mather]

Servers24,

Hi Philip,
 
Thank you very much for your help.

Can you please tell me the differemce between COUNT(*) and COUNT(id) ?
Thanks again.
Actually sorry I was a bit misleading there. MySQL is optimized to 
calculate...

   SELECT COUNT(*) FROM aTable;
...but given the fact you've got a where condition that optimizations 
probably no longer applicable, so I'd stick with...

   SELECT COUNT(id) FROM sent WHERE member_id = ...;
...the real difference would be when you do a SELECT COUNT(DISTINCT id) 
instead of a SELECT COUNT(DISTINCT *) which are different questions but 
not relevant to your situation.


Regards,
   Phil

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: Count the number of specific rows

[Peter Brawley]

>I can simply use this :
>SELECT id FROM sent WHERE member_id= ...
>and the use count($result) to count the number, but I want a faster 
way, if

>possible.

SELECT COUNT(id) FROM sent WHERE member_id= ...

PB

Servers24 Network wrote:

Hi,

Well this question may seem funny, but I really need to know!
The problem is with counting a user's contribution in my site. Suppose 
that

each user that send an email will be stored in DB. Now I want to count
number of times that a user has sent an email.
I can simply use this :
SELECT id FROM sent WHERE member_id= ...
and the use count($result) to count the number, but I want a faster 
way, if

possible.
Thanks

PS : This is *MySQL 4.0.x*



No virus found in this incoming message.
Checked by AVG Free Edition.
Version: 7.1.409 / Virus Database: 268.15.29/607 - Release Date: 12/28/2006

  



--
No virus found in this outgoing message.
Checked by AVG Free Edition.
Version: 7.1.409 / Virus Database: 268.15.29/607 - Release Date: 12/28/2006


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: Count the number of specific rows

[Philip Mather]

Servers24,

Well this question may seem funny...
No, a funny question would start something like "Why did the nun cross 
the road?". ;^)


The problem is with counting a user's contribution in my site. Suppose 
that

each user that send an email will be stored in DB. Now I want to count
number of times that a user has sent an email.
I can simply use this :
SELECT id FROM sent WHERE member_id= ...
and the use count($result) to count the number, but I want a faster 
way, if

Get MySQL to do the counting...
   "SELECT COUNT(*) FROM sent WHERE member_id = ...;"
...saying COUNT(*) is certainly no slower than saying COUNT(id) and the 
COUNT function is certainly faster than iterating over the result set 
using whatever language, also make sure you have indexed the member_id 
field as well.
   Alternatively you maybe able to incorporate a **|SQL_CALC_FOUND_ROWS 
|**into an existing query and then do a  **| |**|FOUND_ROWS()| to 
optimize things (see 
http://dev.mysql.com/doc/refman/4.1/en/information-functions.html) or 
failing that run a nightly query to compile the stats into a new table 
containing just member_id and emails_contributed fields?

   Those would be the standard options.

Regards,
   Phil

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: Count the number of specific rows

[Duncan Hill]

On Friday 29 December 2006 14:02, Servers24 Network wrote:
> Hi,
>
> Well this question may seem funny, but I really need to know!
> The problem is with counting a user's contribution in my site. Suppose that
> each user that send an email will be stored in DB. Now I want to count
> number of times that a user has sent an email.
> I can simply use this :
> SELECT id FROM sent WHERE member_id= ...
> and the use count($result) to count the number, but I want a faster way, if
> possible.

select count(id) from sent.
-- 
Scanned by iCritical.

-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: Count query?

[Dwight Tovey]

chris smith wrote:
> On 10/21/06, Dwight Tovey <[EMAIL PROTECTED]> wrote:
>> Hello all
>> Maybe it's been a long week, but I'm trying to do something that should
>> be
>> simple and just not getting anywhere.
>>
>> I have two tables:
>> accounts
>>   acctid: int unique
>>   acctowner: char
>>   ...
>>
>> docs
>>   docid: int unique
>>   acctid: int
>>   doctitle: char
>>   ...
>>
>> I want to list my accounts along with a count of the documents
>> associated
>> with each account.  I tried a simple GROUP BY:
>> SELECT a.acctid, a.acctowner, COUNT(d.acctid)
>> FROM accounts AS a, documents AS d
>> WHERE a.acctid = d.acctid
>> GROUP BY a.acctid;
>
> Change it to a left join:
>
> ... FROM accounts a LEFT OUTER JOIN documents d ON a.acctid=d.acctid
> GROUP BY a.acctid;
>

Bingo.  I new it had to be something simple.  I didn't even think of an
outer join.  It all works fine now.  This should save me from having to
select the accounts, then loop through to count the documents for each
account.  I didn't like that idea at all.

Thanks again.

/dwight

-- 
Dwight N. Tovey
[EMAIL PROTECTED]
http://www.dtovey.net/~dwight/
Please Do Not send me Microsoft Word attachments.
See http://www.gnu.org/philosophy/no-word-attachments.html
---
About the time we think we can make ends meet, somebody moves the ends.


-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: Count query?

[chris smith]

On 10/21/06, Dwight Tovey <[EMAIL PROTECTED]> wrote:

Hello all
Maybe it's been a long week, but I'm trying to do something that should be
simple and just not getting anywhere.

I have two tables:
accounts
  acctid: int unique
  acctowner: char
  ...

docs
  docid: int unique
  acctid: int
  doctitle: char
  ...

I want to list my accounts along with a count of the documents associated
with each account.  I tried a simple GROUP BY:
SELECT a.acctid, a.acctowner, COUNT(d.acctid)
FROM accounts AS a, documents AS d
WHERE a.acctid = d.acctid
GROUP BY a.acctid;


Change it to a left join:

... FROM accounts a LEFT OUTER JOIN documents d ON a.acctid=d.acctid
GROUP BY a.acctid;

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: Count of children

[Douglas Sims]

By default it doesn't, but you can change that behaviour.  Quoting  
from this page http://dev.mysql.com/doc/refman/5.0/en/stored- 
procedures.html :


Recursive stored procedures are disabled by default, but can be  
enabled on the server by setting the max_sp_recursion_depth server  
system variable to a nonzero value. See Section 5.2.3, “System  
Variables”, for more information.


There is also a very thorough article discussing stored procedures in  
MySQL which gives an example of tree traversal here:

http://dev.mysql.com/tech-resources/articles/mysql-storedprocedures.html

Douglas Sims
[EMAIL PROTECTED]



On Sep 27, 2006, at 9:40 AM, João Cândido de Souza Neto wrote:


You must do that by a some language script, unfortunatly mysql is no
recursive.

""André Hänsel"" <[EMAIL PROTECTED]> escreveu na mensagem
news:[EMAIL PROTECTED]
Sorry, my english sentence was imprecise. :) I want the count of all
sub-entries AND THEIR sub-sub-entries.

Example:

A
   / \
   B C
/ \
D E
   \
   F

So I want to know that C has 3 sub-nodes.



-Ursprüngliche Nachricht-
Von: Rob Desbois [mailto:[EMAIL PROTECTED]
Gesendet: Mittwoch, 27. September 2006 15:48
An: André Hänsel; mysql@lists.mysql.com
Betreff: re: Count of children

André,

Your sentence 'I want the count of all sub-entries for a
specific entry' converts straight into SQL:

'I want'
SELECT
the count of all entries
COUNT(*) FROM myTable
with a specific parent
WHERE parent_id = 5

You've missed one of the major benefits of SQL - it's
designed to read like an English sentence!
--Rob


_ 
_

This email has been scanned by the MessageLabs Email Security System.
For more information please visit http://www.messagelabs.com/email
_ 
_


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:
http://lists.mysql.com/[EMAIL PROTECTED]





--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]



--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: Count of children

[Peter Brawley]

André,


I want the count of all sub-entries for a specific entry.


Depends on the model you are using--edge list or nested sets?

PB

-

André Hänsel wrote:

I have a table with id and parent_id.
I want the count of all sub-entries for a specific entry.

I found several documents about working with graphs/trees in MySQL but I
could not find a solution for my problem.

I can imagine two possibilities, but one is memory intensive and the other
one creates load on updates.
The first is, that I select all entries and then use a procedural language
to determine recursively whether an node is a sub-node of the specific node.
The second is, that I store the sub-node count with each node and when I do
an insert, I walk the tree upwards and increment the node-counts.

Is there a smart solution/best practice for my problem?

Now I can't think of another sentence starting with an i. ;-)

Best regards,
André


  



--
No virus found in this outgoing message.
Checked by AVG Free Edition.
Version: 7.1.407 / Virus Database: 268.12.9/457 - Release Date: 9/26/2006


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: Count of children

[Jo�o C�ndido de Souza Neto]

You must do that by a some language script, unfortunatly mysql is no 
recursive.

""André Hänsel"" <[EMAIL PROTECTED]> escreveu na mensagem 
news:[EMAIL PROTECTED]
Sorry, my english sentence was imprecise. :) I want the count of all
sub-entries AND THEIR sub-sub-entries.

Example:

A
   / \
   B C
/ \
D E
   \
   F

So I want to know that C has 3 sub-nodes.


> -Ursprüngliche Nachricht-
> Von: Rob Desbois [mailto:[EMAIL PROTECTED]
> Gesendet: Mittwoch, 27. September 2006 15:48
> An: André Hänsel; mysql@lists.mysql.com
> Betreff: re: Count of children
>
> André,
>
> Your sentence 'I want the count of all sub-entries for a
> specific entry' converts straight into SQL:
>
> 'I want'
> SELECT
> the count of all entries
> COUNT(*) FROM myTable
> with a specific parent
> WHERE parent_id = 5
>
> You've missed one of the major benefits of SQL - it's
> designed to read like an English sentence!
> --Rob
>
>
> __
> This email has been scanned by the MessageLabs Email Security System.
> For more information please visit http://www.messagelabs.com/email
> __
>
> -- 
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe:
> http://lists.mysql.com/[EMAIL PROTECTED]
>



-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

re: Count of children

[Rob Desbois]

André,

Your sentence 'I want the count of all sub-entries for a specific entry' 
converts straight into SQL:

'I want'
SELECT
the count of all entries
COUNT(*) FROM myTable
with a specific parent
WHERE parent_id = 5

You've missed one of the major benefits of SQL - it's designed to read like an 
English sentence!
--Rob


__
This email has been scanned by the MessageLabs Email Security System.
For more information please visit http://www.messagelabs.com/email 
__

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: Count of children

[Jo�o C�ndido de Souza Neto]

You can use it:

SELECT parent_id, count( * )
FROM table
WHERE parent_id =1
GROUP BY parent_id

It´ll works fine.


""André Hänsel"" <[EMAIL PROTECTED]> escreveu na mensagem 
news:[EMAIL PROTECTED]
I have a table with id and parent_id.
I want the count of all sub-entries for a specific entry.

I found several documents about working with graphs/trees in MySQL but I
could not find a solution for my problem.

I can imagine two possibilities, but one is memory intensive and the other
one creates load on updates.
The first is, that I select all entries and then use a procedural language
to determine recursively whether an node is a sub-node of the specific node.
The second is, that I store the sub-node count with each node and when I do
an insert, I walk the tree upwards and increment the node-counts.

Is there a smart solution/best practice for my problem?

Now I can't think of another sentence starting with an i. ;-)

Best regards,
André



-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: Count Fields of a Table

[Ivan Aleman]

Hello,
@Mr. Price and Mr. Sims

Thank you for show me the way, your tips where very educational.

Thanks again.

--
Iván Alemán ~ [[ Debian (Sid) ]] ~
-BEGIN GEEK CODE BLOCK-
Version: 3.12
G!>GCM d+ s: a? C+++ UL++ P L+>+++$ E--- W++>+ N* o--- K- w O- M+ V--
PS++ PE-- Y PGP+>++ t-- 5 X R+ !tv b++ DI-- D+++ G+ e++ h* r+ z*>*$
--END GEEK CODE BLOCK--
bonovoxmofo.blogspot.com

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: Count Fields of a Table

[Ivan Aleman]

[...]

Is there any way to find out, using only plain SQL, the number of fields
of a given table.

describe gives me the number of fields as result, but I need to get only
that.

Is it possible?

Is it also possible to get only the fields name?


AFIK there's no easy way to accomplish this using just plain SQL.
Every time you use DESCRIBE tbl_name you get the number of columns
counted as rows.

You can also use

SHOW COLUMNS FROM tbl_name
SHOW FIELDS FROM tbl_name

And from the shell you can also type

mysqlshow -u myuser -p db_name tbl_name


--
Iván Alemán ~ [[ Debian (Sid) ]] ~
-BEGIN GEEK CODE BLOCK-
Version: 3.12
G!>GCM d+ s: a? C+++ UL++ P L+>+++$ E--- W++>+ N* o--- K- w O- M+ V--
PS++ PE-- Y PGP+>++ t-- 5 X R+ !tv b++ DI-- D+++ G+ e++ h* r+ z*>*$
--END GEEK CODE BLOCK--
bonovoxmofo.blogspot.com

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

RE: Count Fields of a Table

[Price, Randall]

David,

For the count of columns in a table:

SELECT count(information_schema.columns.column_name)
FROM   information_schema.columns
WHERE  information_schema.columns.table_schema = 'database_name'
ANDinformation_schema.columns.table_name = 'table_name'

For the names of the columns in a table:

SELECT information_schema.columns.column_name
FROM   information_schema.columns
WHERE  information_schema.columns.table_schema = 'database_name'
ANDinformation_schema.columns.table_name = 'table_name'

Hope this helps.

Randall Price

Microsoft Implementation Group
Secure Enterprise Computing Initiatives
Virginia Tech Information Technology
1700 Pratt Drive
Blacksburg, VA  24060

Email:  [EMAIL PROTECTED]
Phone:  (540) 231-4396

-Original Message-
From: davidvaz [mailto:[EMAIL PROTECTED] 
Sent: Friday, September 22, 2006 10:54 AM
To: mysql@lists.mysql.com
Subject: Count Fields of a Table

Hello,

Is there any way to find out, using only plain SQL, the number of fields
of a given table.

describe gives me the number of fields as result, but I need to get only
that.

Is it possible?

Is it also possible to get only the fields name?

Thanks

David


-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:
http://lists.mysql.com/[EMAIL PROTECTED]


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: Count Fields of a Table

[Douglas Sims]

Hi David

If you are using mysql 5.0 and up, you can select from the  
"INFORMATION_SCHEMA" database to get this information and much more.


Following is an example using a database called "test" and a table  
called "t"


To get the column names, use
SELECT column_name FROM information_schema.columns WHERE  
table_schema='test' AND table_name='t';


mysql> use test;
Database changed
mysql> describe t;
+-+--+--+-+-+---+
| Field   | Type | Null | Key | Default | Extra |
+-+--+--+-+-+---+
| TransactionDate | datetime | YES  | | |   |
| amount  | float| YES  | | |   |
+-+--+--+-+-+---+
2 rows in set (0.07 sec)

mysql> select * from information_schema.columns where  
table_schema='test' and table_name='t';
+---+--++- 
+--++-+--- 
+--+ 
+---+---+ 
++-++--- 
+-++
| TABLE_CATALOG | TABLE_SCHEMA | TABLE_NAME | COLUMN_NAME |  
ORDINAL_POSITION | COLUMN_DEFAULT | IS_NULLABLE | DATA_TYPE |  
CHARACTER_MAXIMUM_LENGTH | CHARACTER_OCTET_LENGTH | NUMERIC_PRECISION  
| NUMERIC_SCALE | CHARACTER_SET_NAME | COLLATION_NAME | COLUMN_TYPE |  
COLUMN_KEY | EXTRA | PRIVILEGES  | COLUMN_COMMENT |
+---+--++- 
+--++-+--- 
+--+ 
+---+---+ 
++-++--- 
+-++
|   | test | t  | TransactionDate |  
1|| YES | datetime  |  
NULL | NULL   | NULL   
| NULL  ||| datetime 
||   | select,insert,update,references  
||
|   | test | t  | amount  |  
2|| YES | float |  
NULL | NULL   | 12 
| NULL  ||| float
||   | select,insert,update,references  
||
+---+--++- 
+--++-+--- 
+--+ 
+---+---+ 
++-++--- 
+-++

2 rows in set (0.01 sec)

mysql> select count(*) from information_schema.columns where  
table_schema='test' and table_name='t';

+--+
| count(*) |
+--+
| 2|
+--+
1 row in set (0.32 sec)

mysql> select column_name from information_schema.columns where  
table_schema='test' and table_name='t';

+-+
| column_name |
+-+
| TransactionDate |
| amount  |
+-+
2 rows in set (0.08 sec)


Douglas Sims
[EMAIL PROTECTED]



On Sep 22, 2006, at 9:54 AM, davidvaz wrote:


Hello,

Is there any way to find out, using only plain SQL, the number of  
fields

of a given table.

describe gives me the number of fields as result, but I need to get  
only

that.

Is it possible?

Is it also possible to get only the fields name?

Thanks

David


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]




--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: COUNT question

[Jørn Dahl-Stamnes]

On Monday 18 September 2006 14:55, Brent Baisley wrote:
> You might try changing it to distinct if you are looking for unique count
> of ids from each. SELECT a.a,aa,COUNT(DISTINCT b.id),COUNT(DISTINCT c.id)
> FROM...

This return 0 or 1 for b.id (1 if there is 1 or more records) and the correct 
value for the c.id.


-- 
Jørn Dahl-Stamnes
homepage: http://www.dahl-stamnes.net/dahls/

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: COUNT question

[Brent Baisley]

You might try changing it to distinct if you are looking for unique count of 
ids from each.
SELECT a.a,aa,COUNT(DISTINCT b.id),COUNT(DISTINCT c.id) FROM...

Since you are doing a left join, there always going to be something for b.id and c.id, even if the "value" is NULL. Distinct may 
work to filter out duplicates like NULL. Otherwise you'll need to do it long hand:

SELECT a.a,aa,COUNT(IF(b.id IS NULL,0,1)),COUNT(IF(c.id IS NULL,0,1)) FROM...

- Original Message - 
From: "Jørn Dahl-Stamnes" <[EMAIL PROTECTED]>

To: 
Sent: Monday, September 18, 2006 6:39 AM
Subject: COUNT question


I have a query like:

SELECT a.a,aa,COUNT(b.id),COUNT(c.id) FROM a LEFT JOIN b ON (b.a_ref=a.id)
LEFT JOIN c ON (c.a_ref=a.id);

But it seems like SQL is mixing up the two count's. I get the count from table
c instead of table b for the first occurence of COUNT in the query.

Can I use two COUNT's in one query or do I have to split the query into two
queries?

--
Jørn Dahl-Stamnes
homepage: http://www.dahl-stamnes.net/dahls/

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: COUNT question

[Johan Höök]

Hi Jörn,
I don't think you can do it in one SELECT
as you'll get the same number (the max)
as soon as the COUNT goes above zero.
If you think about how your resultset looks
if you remove your COUNTs it becomes clearer.
Say that for one a.a you have 3 matches in b
and 2 matches in c, this will result in three
rows. COUNT this and you will have an unpredictable result.

However you can solve it in one go with something like this:

SELECT Id, SUM(Bid), SUM(Cid)
FROM (SELECT a.Id AS Id, COUNT(b.Id) AS Bid, 0 AS Cid
FROM tablea a
LEFT JOIN tableb b ON b.a_ref=a.id
GROUP BY a.Id
   UNION ALL
 SELECT a.Id AS Id, 0 AS Bid, COUNT(c.Id) AS Cid
FROM tablea a
LEFT JOIN tablec c ON c.a_ref=a.id
GROUP BY a.Id
 ) AS tabled
GROUP BY Id

There is more then one select, but only one query...

/Johan


Jørn Dahl-Stamnes skrev:

I have a query like:

SELECT a.a,aa,COUNT(b.id),COUNT(c.id) FROM a LEFT JOIN b ON (b.a_ref=a.id) 
LEFT JOIN c ON (c.a_ref=a.id);


But it seems like SQL is mixing up the two count's. I get the count from table 
c instead of table b for the first occurence of COUNT in the query.


Can I use two COUNT's in one query or do I have to split the query into two 
queries?





-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: COUNT() Efficiency

[Rhino]

- Original Message - 
From: "Martin Gallagher" <[EMAIL PROTECTED]>

To: 
Sent: Saturday, April 08, 2006 6:34 PM
Subject: COUNT() Efficiency



Hi,

If I did a query like:

SELECT COUNT(id) AS count1, COUNT(id) AS count2 FROM table WHERE id<100

Would MySQL run the COUNT() calculation once or twice?



I don't know the answer to your question but why would you want to count in 
the same column of the same table twice in the same query?


I'm not very knowledgeable about MySQL performance - I'm mostly a DB2 guy - 
but doesn't MySQL have an Explain command that will tell you what access 
path you are getting? If it does, your best bet would be to try your query 
and do an Explain to see what it actually does; it should be pretty clear 
whether the count() is being done once or twice.


--
Rhino 




--
No virus found in this outgoing message.
Checked by AVG Free Edition.
Version: 7.1.385 / Virus Database: 268.4.0/304 - Release Date: 07/04/2006


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: count(*) send a wrong value

[Fabien SK]

Le mardi 24 janvier 2006 à 19:23 +0100, Fabien SK a écrit :
> Le mardi 24 janvier 2006 à 19:08 +0100, fabsk a écrit :
> > Thank you a lot for your answer. The bug is there: if I drop the index 
> > 'tp_idx_part_solution', the result of the count is OK.
> > I recreated this index and the cound drop to "2" again.
> 
> It doesn't happen on version 4.1.12-Max on my machine (the version on my
> web provider is 4.1.15). So it seems that it could be an old bug (or
> maybe I am lucky on my machine).

If found that it is this bug:

http://bugs.mysql.com/bug.php?id=14980



-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: count(*) send a wrong value

[Fabien SK]

Le mardi 24 janvier 2006 à 19:08 +0100, fabsk a écrit :
> Thank you a lot for your answer. The bug is there: if I drop the index 
> 'tp_idx_part_solution', the result of the count is OK.
> I recreated this index and the cound drop to "2" again.

It doesn't happen on version 4.1.12-Max on my machine (the version on my
web provider is 4.1.15). So it seems that it could be an old bug (or
maybe I am lucky on my machine).

Fabien


-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: count(*) send a wrong value

[fabsk]

Le mardi 24 janvier 2006 à 09:19 +0100, Martijn Tonies a écrit :
>> CREATE TABLE `tp_participation` (
>>   `uid` int(11) NOT NULL default '0',
>>   `challenge_id` int(11) NOT NULL default '0',
>>   `response` text collate latin1_general_ci NOT NULL,
>>   `points` int(11) default NULL,
>>   UNIQUE KEY `tp_id_part` (`uid`,`challenge_id`),
>>   KEY `tp_idx_part_solution` (`challenge_id`,`response`(4))
> ) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci;
> > select * from tp_participation where challenge_id=20
> > > 10 records.
> >
> > select challenge_id from tp_participation where challenge_id=20
> > > _two_ records with the value "20".
>
> What are the results if you drop all constraints and indices?
> 

Thank you a lot for your answer. The bug is there: if I drop the index 
'tp_idx_part_solution', the result of the count is OK.
I recreated this index and the cound drop to "2" again.

Fabien


-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: count(*) send a wrong value

[Martijn Tonies]

> Thank you a lot for your answer. I checked very carefully. The structure
> of the table is (exported by phpMyAdmin):
>
> CREATE TABLE `tp_participation` (
>   `uid` int(11) NOT NULL default '0',
>   `challenge_id` int(11) NOT NULL default '0',
>   `response` text collate latin1_general_ci NOT NULL,
>   `points` int(11) default NULL,
>   UNIQUE KEY `tp_id_part` (`uid`,`challenge_id`),
>   KEY `tp_idx_part_solution` (`challenge_id`,`response`(4))
> ) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci;
>
>
> I removed the key on challenge_id as suggested in another post.
>
> I do the following queries with phpMyAdmin (no direct access to mysql
> client):
>
> select * from tp_participation where challenge_id=20
> > 10 records.
>
> select challenge_id from tp_participation where challenge_id=20
> > _two_ records with the value "20".
>
> select count(challenge_id) from tp_participation where challenge_id=20
> > One record with value "2"
>
> select count(*) from tp_participation where challenge_id=20
> > One record with value "2"
>
> select count(uid) from tp_participation where challenge_id=20
> > One record with value "10"
>
> select count(response) from tp_participation where challenge_id=20
> > One record with value "10"
>
> select count(points) from tp_participation where challenge_id=20
> > One record with value "select count(response) from tp_participation
> where challenge_id=20
> > One record with value "0" (value of points is null for each field with
> challenge_id in the database, so it seems normal)

What are the results if you drop all constraints and indices?

Martijn Tonies
Database Workbench - tool for InterBase, Firebird, MySQL, Oracle & MS SQL
Server
Upscene Productions
http://www.upscene.com
Database development questions? Check the forum!
http://www.databasedevelopmentforum.com


-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: count(*) send a wrong value

[fabsk]

Hi,

Thank you a lot for your answer. I checked very carefully. The structure
of the table is (exported by phpMyAdmin):

CREATE TABLE `tp_participation` (
  `uid` int(11) NOT NULL default '0',
  `challenge_id` int(11) NOT NULL default '0',
  `response` text collate latin1_general_ci NOT NULL,
  `points` int(11) default NULL,
  UNIQUE KEY `tp_id_part` (`uid`,`challenge_id`),
  KEY `tp_idx_part_solution` (`challenge_id`,`response`(4))
) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci;


I removed the key on challenge_id as suggested in another post.

I do the following queries with phpMyAdmin (no direct access to mysql
client):

select * from tp_participation where challenge_id=20
> 10 records.

select challenge_id from tp_participation where challenge_id=20
> _two_ records with the value "20".

select count(challenge_id) from tp_participation where challenge_id=20
> One record with value "2"

select count(*) from tp_participation where challenge_id=20
> One record with value "2"

select count(uid) from tp_participation where challenge_id=20
> One record with value "10"

select count(response) from tp_participation where challenge_id=20
> One record with value "10"

select count(points) from tp_participation where challenge_id=20
> One record with value "select count(response) from tp_participation
where challenge_id=20
> One record with value "0" (value of points is null for each field with
challenge_id in the database, so it seems normal)

Fabien

Le lundi 23 janvier 2006 à 17:50 -0500, Rhino a écrit :
> What you're describing definitely sounds like a bug to me, assuming that you 
> are accurately reporting the query you've used and the data in your table.
> 
> In other words, if there really are 10 rows that have a cid value of 123 and 
> you really are doing "select * from table where cid = 123", then you should 
> definitely be getting a result of 10, not 2.
> 
> But that is a VERY strange error to be having! I've been writing SQL for 
> over 20 years on a variety of platforms and I can't remember EVER seeing a 
> count(*) give the wrong result. Any time the result was not what I expected, 
> it turned out that I'd written the query incorrectly or I was wrong about 
> what data was in the table. I'd also expect that the MySQL testing team 
> would have executed many tests to be sure that basic functionality like 
> count(*) works before ever releasing the product.
> 
> Please, retest everything VERY carefully once more and make VERY sure that 
> you aren't inadvertently writing the query incorrectly and that you really 
> DO have 10 rows with cid = 123. If you still get 2 as the result of your 
> query, I would recommend sending a bug report to MySQL.
> 
> Rhino
> 
> - Original Message - 
> From: "fabsk" <[EMAIL PROTECTED]>
> To: 
> Sent: Monday, January 23, 2006 5:32 PM
> Subject: Re: count(*) send a wrong value
> 
> 
> > Thank you for you answer, but I read many times and I did not found
> > something to answer my question (well, I did not know about the NULL).
> >
> > In my case:
> > - there is one table
> > - htere is no "distinct"
> > - there is a WHERE clause, so there is no optimisation
> > - there is no other field and no "group by"
> >
> > If I do "count(cid)", I still get "2".
> >
> > Fabien
> >
> > Le lundi 23 janvier 2006 à 20:54 +, [EMAIL PROTECTED] a écrit :
> >> >From the MySQL 4.1 manual
> >>
> >> 12.10.1. GROUP BY (Aggregate) Functions
> >>
> >> COUNT(expr)
> >>
> >> Returns a count of the number of non-NULL values in the rows
> >> retrieved by a SELECT statement.
> >>
> >>
> >> COUNT() returns 0 if there were no matching rows.
> >>
> >> mysql> SELECT student.student_name,COUNT(*)
> >> ->FROM student,course
> >> ->WHERE student.student_id=course.student_id
> >> ->GROUP BY student_name;
> >>
> >>
> >> COUNT(*) is somewhat different in that it returns a count
> >> of the number of rows retrieved, whether or not they contain
> >> NULL values.
> >>
> >>
> >> COUNT(*) is optimized to return very quickly if the SELECT
> >> retrieves from one table, no other columns are retrieved,
> >> and there is no WHERE clause. For example:
> >>
> >> mysql> SELECT COUNT(*) FROM student;
> >>
> >>
> >>  This optimization applies only to MyISAM and ISAM tables
> >> only, 

Re: count(*) send a wrong value

[Hank]

My guess would that your PHP code is not written correctly.

For instance, if you have a query in PHP:

$sql="select * from my_table where cid=123";

...and are using the PHP function mysql_numrows() to count the
results, and then for your next test... you're just changing the query
to:

$sql="select count(*) from my_table where cid=123"

and still using the mysql_numrows()  to get the result, that is your
error.  You'll need to use mysql_result() or some other fetch function
to get the results of the query.

That's my guess.

-Hank

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: count(*) send a wrong value

[Michael Stassen]

fabsk wrote:
> Hi,
>
> I'm facing a strange problem. I am using a database at my Internet
> provider (Free, France). The type of table is MyISAM (no choice), MySQL
> 4.1.15. I can do my tests with my PHP code or phpMyAdmin.
>
> The definition of my table is:
> - uid, int
> - cid, int
> - response, text
> - points, int (can be null)
>
> keys:
> - uid, cid
> - cid, response(4)
> - cid

If I'm reading this correctly, the third index is redundant.  The multi-column 
index on (cid, response(4)) will function equally well as an index on cid. 
There's no need for a separate single-column index on cid.


> When I do "select * from my_table where cid=123", I get my 10 records.
> But when I do "select count(*) from my_table where cid=123" I get "2". I
> also happens with many other values of "cid" and the bad result is
> always "2".
>
> I can't understand what's happen. It seems to simple, but there should
> be something. Do you have an idea?
>
> Thank you for your attention
> Fabien

[EMAIL PROTECTED] wrote:

From the MySQL 4.1 manual

12.10.1. GROUP BY (Aggregate) Functions

COUNT(expr) 

Returns a count of the number of non-NULL values in the rows 
retrieved by a SELECT statement. 

COUNT() returns 0 if there were no matching rows. 


mysql> SELECT student.student_name,COUNT(*)
->FROM student,course
->WHERE student.student_id=course.student_id
->GROUP BY student_name;


COUNT(*) is somewhat different in that it returns a count 
of the number of rows retrieved, whether or not they contain 
NULL values. 



COUNT(*) is optimized to return very quickly if the SELECT 
retrieves from one table, no other columns are retrieved, 
and there is no WHERE clause. For example: 


mysql> SELECT COUNT(*) FROM student;


 This optimization applies only to MyISAM and ISAM tables 
only, because an exact record count is stored for these 
table types and can be accessed very quickly. For 
transactional storage engines (InnoDB, BDB), storing an 
exact row count is more problematic because multiple 
transactions may be occurring, each of which may affect the 
count. 

COUNT(DISTINCT expr,[expr...]) 

Returns a count of the number of different non-NULL values. 

COUNT(DISTINCT) returns 0 if there were no matching rows. 


mysql> SELECT COUNT(DISTINCT results) FROM student;


In MySQL, you can get the number of distinct expression 
combinations that do not contain NULL by giving a list of 
expressions. In standard SQL, you would have to do a 
concatenation of all expressions inside COUNT(DISTINCT ...). 

COUNT(DISTINCT ...) was added in MySQL 3.23.2. 


Keith


Keith, how does any of this explain Fabien's result?  If

  SELECT * FROM my_table WHERE cid=123;

returns 10 rows, then

  SELECT COUNT(*) FROM my_table WHERE cid=123;

must return 10, or something is wrong.


Fabien, if these are your actual queries and results, then there is certainly a 
problem.  One possibility is that your two queries are optimized differently, 
and one of the two indexes starting with cid is broken.  You should probably run 
a CHECK TABLE, then REPAIR TABLE if needed.  See the manual for details 
.


If that doesn't help, show us your real queries and their results, along with 
the EXPLAIN  output for 
each.  The output of SHOW CREATE TABLE 
 would also be 
helpful.


Michael

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: count(*) send a wrong value

[Rhino]

What you're describing definitely sounds like a bug to me, assuming that you 
are accurately reporting the query you've used and the data in your table.


In other words, if there really are 10 rows that have a cid value of 123 and 
you really are doing "select * from table where cid = 123", then you should 
definitely be getting a result of 10, not 2.


But that is a VERY strange error to be having! I've been writing SQL for 
over 20 years on a variety of platforms and I can't remember EVER seeing a 
count(*) give the wrong result. Any time the result was not what I expected, 
it turned out that I'd written the query incorrectly or I was wrong about 
what data was in the table. I'd also expect that the MySQL testing team 
would have executed many tests to be sure that basic functionality like 
count(*) works before ever releasing the product.


Please, retest everything VERY carefully once more and make VERY sure that 
you aren't inadvertently writing the query incorrectly and that you really 
DO have 10 rows with cid = 123. If you still get 2 as the result of your 
query, I would recommend sending a bug report to MySQL.


Rhino

- Original Message - 
From: "fabsk" <[EMAIL PROTECTED]>

To: 
Sent: Monday, January 23, 2006 5:32 PM
Subject: Re: count(*) send a wrong value



Thank you for you answer, but I read many times and I did not found
something to answer my question (well, I did not know about the NULL).

In my case:
- there is one table
- htere is no "distinct"
- there is a WHERE clause, so there is no optimisation
- there is no other field and no "group by"

If I do "count(cid)", I still get "2".

Fabien

Le lundi 23 janvier 2006 à 20:54 +, [EMAIL PROTECTED] a écrit :

>From the MySQL 4.1 manual

12.10.1. GROUP BY (Aggregate) Functions

COUNT(expr)

Returns a count of the number of non-NULL values in the rows
retrieved by a SELECT statement.


COUNT() returns 0 if there were no matching rows.

mysql> SELECT student.student_name,COUNT(*)
->FROM student,course
->WHERE student.student_id=course.student_id
->GROUP BY student_name;


COUNT(*) is somewhat different in that it returns a count
of the number of rows retrieved, whether or not they contain
NULL values.


COUNT(*) is optimized to return very quickly if the SELECT
retrieves from one table, no other columns are retrieved,
and there is no WHERE clause. For example:

mysql> SELECT COUNT(*) FROM student;


 This optimization applies only to MyISAM and ISAM tables
only, because an exact record count is stored for these
table types and can be accessed very quickly. For
transactional storage engines (InnoDB, BDB), storing an
exact row count is more problematic because multiple
transactions may be occurring, each of which may affect the
count.


COUNT(DISTINCT expr,[expr...])


Returns a count of the number of different non-NULL values.


COUNT(DISTINCT) returns 0 if there were no matching rows.

mysql> SELECT COUNT(DISTINCT results) FROM student;


In MySQL, you can get the number of distinct expression
combinations that do not contain NULL by giving a list of
expressions. In standard SQL, you would have to do a
concatenation of all expressions inside COUNT(DISTINCT ...).

COUNT(DISTINCT ...) was added in MySQL 3.23.2.

Keith

In theory, theory and practice are the same;
In practice they are not.

On Mon, 23 Jan 2006, fabsk wrote:

> To: mysql@lists.mysql.com
> From: fabsk <[EMAIL PROTECTED]>
> Subject: count(*) send a wrong value
>
> Hi,
>
> I'm facing a strange problem. I am using a database at my Internet
> provider (Free, France). The type of table is MyISAM (no choice),

MySQL

> 4.1.15. I can do my tests with my PHP code or phpMyAdmin.
>
> The definition of my table is:
> - uid, int
> - cid, int
> - response, text
> - points, int (can be null)
>
> keys:
> - uid, cid
> - cid, response(4)
> - cid
>
> When I do "select * from my_table where cid=123", I get my 10

records.

> But when I do "select count(*) from my_table where cid=123" I get

"2". I

> also happens with many other values of "cid" and the bad result is
> always "2".
>
> I can't understand what's happen. It seems to simple, but there

should

> be something. Do you have an idea?
>
> Thank you for your attention
> Fabien
>
>
> -- 
> MySQL General Mailing List

> For list archives: http://lists.mysql.com/mysql
> To unsubscribe:

http://lists.mysql.com/[EMAIL PROTECTED]

>
>





--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]


--
No virus found in this incoming message.
Checked by AVG Free Edition.
Version: 7.1.375 / Virus Database: 267.14.21

Re: count(*) send a wrong value

[fabsk]

Thank you for you answer, but I read many times and I did not found
something to answer my question (well, I did not know about the NULL).

In my case:
- there is one table
- htere is no "distinct"
- there is a WHERE clause, so there is no optimisation
- there is no other field and no "group by"

If I do "count(cid)", I still get "2".

Fabien

Le lundi 23 janvier 2006 à 20:54 +, [EMAIL PROTECTED] a écrit : 
> >From the MySQL 4.1 manual
> 
> 12.10.1. GROUP BY (Aggregate) Functions
> 
> COUNT(expr) 
> 
> Returns a count of the number of non-NULL values in the rows 
> retrieved by a SELECT statement. 
> 
> 
> COUNT() returns 0 if there were no matching rows. 
> 
> mysql> SELECT student.student_name,COUNT(*)
> ->FROM student,course
> ->WHERE student.student_id=course.student_id
> ->GROUP BY student_name;
> 
> 
> COUNT(*) is somewhat different in that it returns a count 
> of the number of rows retrieved, whether or not they contain 
> NULL values. 
> 
> 
> COUNT(*) is optimized to return very quickly if the SELECT 
> retrieves from one table, no other columns are retrieved, 
> and there is no WHERE clause. For example: 
> 
> mysql> SELECT COUNT(*) FROM student;
> 
> 
>  This optimization applies only to MyISAM and ISAM tables 
> only, because an exact record count is stored for these 
> table types and can be accessed very quickly. For 
> transactional storage engines (InnoDB, BDB), storing an 
> exact row count is more problematic because multiple 
> transactions may be occurring, each of which may affect the 
> count. 
> 
> 
> COUNT(DISTINCT expr,[expr...]) 
> 
> 
> Returns a count of the number of different non-NULL values. 
> 
> 
> COUNT(DISTINCT) returns 0 if there were no matching rows. 
> 
> mysql> SELECT COUNT(DISTINCT results) FROM student;
> 
> 
> In MySQL, you can get the number of distinct expression 
> combinations that do not contain NULL by giving a list of 
> expressions. In standard SQL, you would have to do a 
> concatenation of all expressions inside COUNT(DISTINCT ...). 
> 
> COUNT(DISTINCT ...) was added in MySQL 3.23.2. 
> 
> Keith
> 
> In theory, theory and practice are the same;
> In practice they are not. 
> 
> On Mon, 23 Jan 2006, fabsk wrote:
> 
> > To: mysql@lists.mysql.com
> > From: fabsk <[EMAIL PROTECTED]>
> > Subject: count(*) send a wrong value
> > 
> > Hi,
> > 
> > I'm facing a strange problem. I am using a database at my Internet
> > provider (Free, France). The type of table is MyISAM (no choice),
MySQL
> > 4.1.15. I can do my tests with my PHP code or phpMyAdmin.
> > 
> > The definition of my table is:
> > - uid, int
> > - cid, int
> > - response, text
> > - points, int (can be null)
> > 
> > keys:
> > - uid, cid
> > - cid, response(4)
> > - cid
> > 
> > When I do "select * from my_table where cid=123", I get my 10
records.
> > But when I do "select count(*) from my_table where cid=123" I get
"2". I
> > also happens with many other values of "cid" and the bad result is
> > always "2".
> > 
> > I can't understand what's happen. It seems to simple, but there
should
> > be something. Do you have an idea?
> > 
> > Thank you for your attention
> > Fabien
> > 
> > 
> > -- 
> > MySQL General Mailing List
> > For list archives: http://lists.mysql.com/mysql
> > To unsubscribe:
http://lists.mysql.com/[EMAIL PROTECTED]
> > 
> > 
> 



-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: count(*) send a wrong value

[Fabien SK]

Thank you for you answer, but I read many times and I did not found
something to answer my question (well, I did not know about the NULL).

In my case:
- there is one table
- htere is no "distinct"
- there is a WHERE clause, so there is no optimisation
- there is no other field and no "group by"

If I do "count(cid)", I still get "2".

Fabien

Le lundi 23 janvier 2006 à 20:54 +, [EMAIL PROTECTED] a écrit : 
> >From the MySQL 4.1 manual
> 
> 12.10.1. GROUP BY (Aggregate) Functions
> 
> COUNT(expr) 
> 
> Returns a count of the number of non-NULL values in the rows 
> retrieved by a SELECT statement. 
> 
> 
> COUNT() returns 0 if there were no matching rows. 
> 
> mysql> SELECT student.student_name,COUNT(*)
> ->FROM student,course
> ->WHERE student.student_id=course.student_id
> ->GROUP BY student_name;
> 
> 
> COUNT(*) is somewhat different in that it returns a count 
> of the number of rows retrieved, whether or not they contain 
> NULL values. 
> 
> 
> COUNT(*) is optimized to return very quickly if the SELECT 
> retrieves from one table, no other columns are retrieved, 
> and there is no WHERE clause. For example: 
> 
> mysql> SELECT COUNT(*) FROM student;
> 
> 
>  This optimization applies only to MyISAM and ISAM tables 
> only, because an exact record count is stored for these 
> table types and can be accessed very quickly. For 
> transactional storage engines (InnoDB, BDB), storing an 
> exact row count is more problematic because multiple 
> transactions may be occurring, each of which may affect the 
> count. 
> 
> 
> COUNT(DISTINCT expr,[expr...]) 
> 
> 
> Returns a count of the number of different non-NULL values. 
> 
> 
> COUNT(DISTINCT) returns 0 if there were no matching rows. 
> 
> mysql> SELECT COUNT(DISTINCT results) FROM student;
> 
> 
> In MySQL, you can get the number of distinct expression 
> combinations that do not contain NULL by giving a list of 
> expressions. In standard SQL, you would have to do a 
> concatenation of all expressions inside COUNT(DISTINCT ...). 
> 
> COUNT(DISTINCT ...) was added in MySQL 3.23.2. 
> 
> Keith
> 
> In theory, theory and practice are the same;
> In practice they are not. 
> 
> On Mon, 23 Jan 2006, fabsk wrote:
> 
> > To: mysql@lists.mysql.com
> > From: fabsk <[EMAIL PROTECTED]>
> > Subject: count(*) send a wrong value
> > 
> > Hi,
> > 
> > I'm facing a strange problem. I am using a database at my Internet
> > provider (Free, France). The type of table is MyISAM (no choice), MySQL
> > 4.1.15. I can do my tests with my PHP code or phpMyAdmin.
> > 
> > The definition of my table is:
> > - uid, int
> > - cid, int
> > - response, text
> > - points, int (can be null)
> > 
> > keys:
> > - uid, cid
> > - cid, response(4)
> > - cid
> > 
> > When I do "select * from my_table where cid=123", I get my 10 records.
> > But when I do "select count(*) from my_table where cid=123" I get "2". I
> > also happens with many other values of "cid" and the bad result is
> > always "2".
> > 
> > I can't understand what's happen. It seems to simple, but there should
> > be something. Do you have an idea?
> > 
> > Thank you for your attention
> > Fabien
> > 
> > 
> > -- 
> > MySQL General Mailing List
> > For list archives: http://lists.mysql.com/mysql
> > To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
> > 
> > 
> 


-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: count(*) send a wrong value

[mysql]

>From the MySQL 4.1 manual

12.10.1. GROUP BY (Aggregate) Functions

COUNT(expr) 

Returns a count of the number of non-NULL values in the rows 
retrieved by a SELECT statement. 


COUNT() returns 0 if there were no matching rows. 

mysql> SELECT student.student_name,COUNT(*)
->FROM student,course
->WHERE student.student_id=course.student_id
->GROUP BY student_name;


COUNT(*) is somewhat different in that it returns a count 
of the number of rows retrieved, whether or not they contain 
NULL values. 


COUNT(*) is optimized to return very quickly if the SELECT 
retrieves from one table, no other columns are retrieved, 
and there is no WHERE clause. For example: 

mysql> SELECT COUNT(*) FROM student;


 This optimization applies only to MyISAM and ISAM tables 
only, because an exact record count is stored for these 
table types and can be accessed very quickly. For 
transactional storage engines (InnoDB, BDB), storing an 
exact row count is more problematic because multiple 
transactions may be occurring, each of which may affect the 
count. 


COUNT(DISTINCT expr,[expr...]) 


Returns a count of the number of different non-NULL values. 


COUNT(DISTINCT) returns 0 if there were no matching rows. 

mysql> SELECT COUNT(DISTINCT results) FROM student;


In MySQL, you can get the number of distinct expression 
combinations that do not contain NULL by giving a list of 
expressions. In standard SQL, you would have to do a 
concatenation of all expressions inside COUNT(DISTINCT ...). 

COUNT(DISTINCT ...) was added in MySQL 3.23.2. 

Keith

In theory, theory and practice are the same;
In practice they are not. 

On Mon, 23 Jan 2006, fabsk wrote:

> To: mysql@lists.mysql.com
> From: fabsk <[EMAIL PROTECTED]>
> Subject: count(*) send a wrong value
> 
> Hi,
> 
> I'm facing a strange problem. I am using a database at my Internet
> provider (Free, France). The type of table is MyISAM (no choice), MySQL
> 4.1.15. I can do my tests with my PHP code or phpMyAdmin.
> 
> The definition of my table is:
> - uid, int
> - cid, int
> - response, text
> - points, int (can be null)
> 
> keys:
> - uid, cid
> - cid, response(4)
> - cid
> 
> When I do "select * from my_table where cid=123", I get my 10 records.
> But when I do "select count(*) from my_table where cid=123" I get "2". I
> also happens with many other values of "cid" and the bad result is
> always "2".
> 
> I can't understand what's happen. It seems to simple, but there should
> be something. Do you have an idea?
> 
> Thank you for your attention
> Fabien
> 
> 
> -- 
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
> 
> 

-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: Count two kinds of related records?

[Brian Dunning]

That's exactly what I'm looking for, thanks Eugene.   :)


On Aug 5, 2005, at 12:46 AM, Eugene Kosov wrote:


Brian Dunning wrote:

I'm searching a table of people who own properties, and I want to   
also include the total count of related properties, and the count  
of  related properties whose (status is 'Active' and approval is   
'Active'). I've got:
select accounts.name, count(properties.property_id) as totalcount   
from accounts, properties where   
accounts.account_id=properties.account_id group by  
accounts.account_id;
Works fine. Now I just need to figure out how to add that second   
count of property records meeting the two conditions. Anyone?




I think query below should help you.

  SELECT
accounts.name,
COUNT(properties.property_id) AS totalcount,
SUM(IF(status='Active' AND 'approval='Active', 1, 0))
  FROM accounts LEFT JOIN properties USING(account_id)
  GROUP BY accounts.account_id;


Regards,
Eugene Kosov

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql? 
[EMAIL PROTECTED]






--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: Count two kinds of related records?

[Gleb Paharenko]

Hello.



What do you think about this:



SELECT a.name,

  COUNT(p.property_id) AS totalcount,

SUM( IF(p.status = 'Active' AND p.approval = 'Active', 
1, 0)) AS CCOUNT

FROM accounts a, properties p

WHERE a.account_id = p.account_id 

GROUP BY a.account_id;



The data in tables:

mysql> select * from accounts;

++--+

| account_id | name |

++--+

|  1 | a1   |

|  2 | a2   |

++--+



mysql> select * from properties;

++-++--+

| account_id | property_id | status | approval |

++-++--+

|  1 |   1 | Active | Active   |

|  1 |   2 | Active | Active   |

|  1 |   3 | not-Active | Active   |

|  2 |   3 | not-Active | Active   |

++-++--+



In the results of the query we see 2 'Active' properties which has a1,

as it is in the properties table:



mysql> SELECT a.name,   COUNT(p.property_id) AS totalcount,

SUM( IF(p.status = 'Active' AND p.approval = 'Active', 1, 0)) AS CCOUNT

FROM accounts a, properties p   WHERE a.account_id = p.account_id

GROUP BY a.account_id;

+--+++

| name | totalcount | CCOUNT |

+--+++

| a1   |  3 |  2 |

| a2   |  1 |  0 |

+--+++









Brian Dunning <[EMAIL PROTECTED]> wrote:

> I'm searching a table of people who own properties, and I want to  

> also include the total count of related properties, and the count of  

> related properties whose (status is 'Active' and approval is  

> 'Active'). I've got:

> 

> select accounts.name, count(properties.property_id) as totalcount  

> from accounts, properties where  

> accounts.account_id=properties.account_id group by accounts.account_id;

> 

> Works fine. Now I just need to figure out how to add that second  

> count of property records meeting the two conditions. Anyone?

> 



-- 
For technical support contracts, goto https://order.mysql.com/?ref=ensita
This email is sponsored by Ensita.NET http://www.ensita.net/
   __  ___ ___   __
  /  |/  /_ __/ __/ __ \/ /Gleb Paharenko
 / /|_/ / // /\ \/ /_/ / /__   [EMAIL PROTECTED]
/_/  /_/\_, /___/\___\_\___/   MySQL AB / Ensita.NET
   <___/   www.mysql.com




-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: Count two kinds of related records?

[Eugene Kosov]

Brian Dunning wrote:
I'm searching a table of people who own properties, and I want to  also 
include the total count of related properties, and the count of  related 
properties whose (status is 'Active' and approval is  'Active'). I've got:


select accounts.name, count(properties.property_id) as totalcount  from 
accounts, properties where  accounts.account_id=properties.account_id 
group by accounts.account_id;


Works fine. Now I just need to figure out how to add that second  count 
of property records meeting the two conditions. Anyone?




I think query below should help you.

  SELECT
accounts.name,
COUNT(properties.property_id) AS totalcount,
SUM(IF(status='Active' AND 'approval='Active', 1, 0))
  FROM accounts LEFT JOIN properties USING(account_id)
  GROUP BY accounts.account_id;


Regards,
Eugene Kosov

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: count(*)? was: Re: Query Question...

[Michael Stassen]

stipe42 wrote:

I believe the difference is that count(*) includes nulls (because it is
counting the number of records), whereas count(column) only counts the
records where the column being counted is not null, regardless of the
total number of rows.


Right.  COUNT(*) counts rows, COUNT(col) counts non-null values in col.


Hmm, on a related question then if I am correct above, does
count(distinct column) count NULL as a distinct value or not?  I.e. if
I've got four records in a table with one column: (null, a, a, b), will
count(distinct column) return 3 or 2?


COUNT() doesn't count NULLS.  "SELECT DISTINCT col FROM yourtable" will return 
NULL, 'a', and 'b', but "SELECT COUNT(DISTINCT col) FROM yourtable" will 
return 2, because there are 2 non-NULL values to count.  This is easily verified:


mysql> SELECT * FROM news;
++-+
| id | subject |
++-+
|  1 | cars|
|  2 | toys|
|  3 | books   |
|  4 | NULL|
|  5 | toys|
++-+
5 rows in set (0.00 sec)

mysql> SELECT COUNT(DISTINCT subject) FROM news;
+-+
| COUNT(DISTINCT subject) |
+-+
|   3 |
+-+
1 row in set (0.00 sec)

mysql> SELECT subject, COUNT(*), COUNT(subject) FROM news GROUP BY subject;
+-+--++
| subject | COUNT(*) | COUNT(subject) |
+-+--++
| NULL|1 |  0 |
| books   |1 |  1 |
| cars|1 |  1 |
| toys|2 |  2 |
+-+--++
4 rows in set (0.00 sec)


stipe42


Michael


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: count(*)? was: Re: Query Question...

[stipe42]

I believe the difference is that count(*) includes nulls (because it is
counting the number of records), whereas count(column) only counts the
records where the column being counted is not null, regardless of the
total number of rows.

Hmm, on a related question then if I am correct above, does
count(distinct column) count NULL as a distinct value or not?  I.e. if
I've got four records in a table with one column: (null, a, a, b), will
count(distinct column) return 3 or 2?

stipe42


Nic Stevens wrote:
> Hi ,
> 
> This is a little off topic but I have seen count(*) on this list afew
> times and it got me wondering...
> 
> Is there a reason to use SELECT COUNT(*) as opposed to SELECT COUNT(column)? 
> 
> I have noticed that selecting count(*) versus specifying the column
> name executes much more slowly.
> 
> I've been around, on relative periphery, SQL and SQL DBMS' for a long
> time but never seen count(*) used.
> 
> These days I am more involved with SQL (using MySQL) and want to learn
> more nuances as I go along.
> 
> Cheers and thanks in advance, 
> 
> Nic
> 
> 
> On 7/16/05, stipe42 <[EMAIL PROTECTED]> wrote:
> 
>>Jack Lauman wrote:
>>
>>>Given the following query, how can it be modified to return 'Cuisine'(s)
>>>that have no rows as having a count of zero and also return a SUM of
>>>COUNT(*)?
>>>
>>>SELECT w.WebsiteName, r.Cuisine, COUNT(*)
>>>FROM Restaurant r
>>>JOIN RestaurantWebsites w
>>>ON r.RestaurantID = w.RestaurantID
>>>WHERE w.WebsiteName = 'TOW'
>>>GROUP BY w.WebsiteName, r.Cuisine
>>>
>>>Thanks.
>>>
>>>Jack
>>>
>>>
>>
>>After the where clause tack on:
>>HAVING COUNT(*)>0
>>
>>This will eliminate the zero rows, but won't get you the sum.
>>
>>I think the only way to get the sum is to either run a second query, or
>>to sum up the count column in your code as you're looping through the
>>results.
>>
>>stipe42
>>
>>--
>>MySQL General Mailing List
>>For list archives: http://lists.mysql.com/mysql
>>To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
>>
>>
> 
> 


-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: COUNT (*): Fast if NO where clause. slow with WHERE clause (yes, slow even if index is used).

[Jocelyn Fournier]

Hi,

What about SELECT count(*) FROM table1 - SELECT count(*) FROM table1 
WHERE status = 1 ? (this query should be mush faster)


Regards,
  Jocelyn

jpow wrote:

Hi everyone, I have this problem of slow "count *" when I use a where clause.

1. I have a table of ~1m rows.
2. There is a "status" column which can be 0 or 1.
3. Most of the rows have a status of 0, but maybe 10% of them have a
status of 1.
4. I need to know how many records are status 1 / 0 for
pagination/other purposes.
5. I already have a multi index comprising: id, status

I encountered this issue:

A) SELECT count(*) FROM table1  : Super fast 0.00x secs (select tables
optimized away)

B) SELECT count(*) FROM table 1 WHERE status = 0 : Quite slow ~0.5-0.6
secs (uses where; uses index)


I understand that the the query w/o the where clause is extremely fast
coz the query doesnt even need to access the table index, it can just
retrieve the total no of rows (which is stored)

However, when i put in the WHERE clause, MYSQL needs to access the index.
0.5-0.6 secs is too slow for me, as the db is fast scaling upwards.
Is there any faster way for me to retrieve  the total count for rows
with status = 0/1?

Many Thanks
J Pow



--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

RE: Count(*)

[Gordon Bruce]

select count(distinct ordr_ID) from store 

-Original Message-
From: Gana [mailto:[EMAIL PROTECTED] 
Sent: Thursday, July 07, 2005 3:01 PM
To: mysql@lists.mysql.com
Subject: Count(*)

select count(*) from store group by orederId.

For the above sql, I am not getting the count of unique order ids..

help!!

-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:
http://lists.mysql.com/[EMAIL PROTECTED]




--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

RE: Count(*)

[Jay Blanchard]

[snip]
select count(*) from store group by orederId.

For the above sql, I am not getting the count of unique order ids..
[/snip]

select orderID, count(*) from store group by orderID

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: Count of multiple columns

[Rhino]

As you have already seen, it is pretty difficult trying to count things in
multiple columns. Although it is probably possible to do the counting, it
might require programming logic in order to count for specific values in the
various columns and then store the subtotals for each column so that they
can be added together for a final total.

A better solution is to redesign your table. You clearly already sense that
it is not an ideal design and you are right. Your data is not properly
normalized. It is not even in First Normal Form because it contains a
repeating group.

Most professional table designers use a process called Normalization to make
sure that their designs avoid the most common problems found in bad designs.
If you Google on "normalization" you can probably find many tutorials on
this subject; this is just one that I've seen which is reasonably good:
http://www.informit.com/articles/article.asp?p=30885&rl=1. I'll let you read
this tutorial - or another one that suits you more - on your own.

In the meantime, let me suggest that your table should look like this:

Description - varchar(100)
Category - varchar(30)
primary key(Description, Category)

You really don't need the ID column: it doesn't add anything useful to the
data and the Description is much more useful. The Primary Key clause is very
important. It must contain both the Description and the Category: if it
contained only the Description, then you would be limited to only one
Category for each Description. If you add Category to the Primary Key, you
can now have as many categories as you like for each Description but you can
be sure that any given Description can only have a specific Category once.
In other words, it is not a problem to have *one* row that for Description
'aaa' that says the Category is 'Food' but the primary key will ensure that
you can never have MORE than one row with a Category of 'Food' for
description 'aaa'.

Then, the data will look like this:

DescriptionCategory
aaa   Food
aaa   America
aaa   Cheese
bbb   Drink
bbb   America
bbb   Wines
cccWines
cccDrink
ddd   America
ddd   Food

If you want to find the number of rows containing 'America':

select count(*)
from mytable
where Category = 'America'

To get the number of rows containing 'Wines', just replace 'America' with
'Wines' in the query.


Rhino

- Original Message - 
From: "Micha Berdichevsky" <[EMAIL PROTECTED]>
To: 
Sent: Sunday, May 08, 2005 2:07 AM
Subject: Count of multiple columns


> Hi.
> I have a table that store different items. For each items, I can attach
> up to 5 different textual categories.
> Those categories are free-text, and different columns can have the same
> values (example below).
> I am trying to select the count of each of those categories, regardless
> of it's position.
> The table looks like:
> ID - int(11) auto_increment,
> Description - varchar(100),
> Cat1 - varchar(30),
> Cat2 - varchar(30),
> Cat3 - varchar(30),
> Cat4 - varchar(30),
> Cat5 - varchar(30) ... (etc).
>
> Sample data may be:
> 1, "aaa", "Food", "America", "Cheese", NULL, NULL
> 2, "bbb", "Drink", "America", "Wines", NULL, NULL
> 3, "ccc", "Wines", "Drink", NULL, NULL, NULL
> 4, "ddd", "America", "Food", NULL, NULL
>
> The result I want is
> Food - 2
> America - 3
> Drink - 2
> Wines - 2
> Cheese - 1
>
> Hope you guys can help (BTW, I'm not too happy with the way the table is
> designed, but couldn't think of a better way).
> Thanks, Micha.
>
>
>
> -- 
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
>
>
> -- 
> No virus found in this incoming message.
> Checked by AVG Anti-Virus.
> Version: 7.0.308 / Virus Database: 266.11.6 - Release Date: 06/05/2005
>
>



-- 
No virus found in this outgoing message.
Checked by AVG Anti-Virus.
Version: 7.0.308 / Virus Database: 266.11.6 - Release Date: 06/05/2005


-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: Count of multiple columns

[mfatene]

Selon Micha Berdichevsky <[EMAIL PROTECTED]>:

> Hi.
> I have a table that store different items. For each items, I can attach
> up to 5 different textual categories.
> Those categories are free-text, and different columns can have the same
> values (example below).
> I am trying to select the count of each of those categories, regardless
> of it's position.
> The table looks like:
> ID - int(11) auto_increment,
> Description - varchar(100),
> Cat1 - varchar(30),
> Cat2 - varchar(30),
> Cat3 - varchar(30),
> Cat4 - varchar(30),
> Cat5 - varchar(30) ... (etc).
>
> Sample data may be:
> 1, "aaa", "Food", "America", "Cheese", NULL, NULL
> 2, "bbb", "Drink", "America", "Wines", NULL, NULL
> 3, "ccc", "Wines", "Drink", NULL, NULL, NULL
> 4, "ddd", "America", "Food", NULL, NULL
>
> The result I want is
> Food - 2
> America - 3
> Drink - 2
> Wines - 2
> Cheese - 1
>
> Hope you guys can help (BTW, I'm not too happy with the way the table is
> designed, but couldn't think of a better way).
> Thanks, Micha.
>
>
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
>
>

Hi,
You can try something not optimized (table structure :o)) like that :

create table if not exists categories(ID int auto_increment primary key,
Description varchar(100),
Cat1 varchar(30),
Cat2 varchar(30),
Cat3 varchar(30),
Cat4 varchar(30),
Cat5 varchar(30));


insert into categories (Description,cat1,cat2,cat3,cat4,cat5) values(
"aaa", "Food", "America", "Cheese", NULL, NULL),("bbb", "Drink", "America",
"Wines", NULL, NULL),
("ccc", "Wines", "Drink", NULL, NULL, NULL),
("ddd", "America", "Food", NULL, NULL,NULL);

select * from categories;

mysql> select res.val,count(res.val) from (select cat1 as val from categories
union all select cat2 as val from categories) res
group by val;
+-++
| val | count(res.val) |
+-++
| America |  3 |
| Drink   |  2 |
| Food|  2 |
| Wines   |  1 |
+-++
4 rows in set (0.00 sec)

Now just add the "union all" you need.


Mathias



-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: COUNT Problem

[Frederic Wenzel]

> >A subselect may help:
> >[...]
> >Don't know ATM if it can be done more easily, but a query like this
> >should probably work.
> >
> It can be done without a sub-query:
> [...]
> 
> That *should* work, barring any typos or ommisions I may have made. I
> used LEFT JOIN because of personal preference, it can be done other way(s).

Ah yes, I see. Nice idea. As JOINs perform better than subselects
IIRC, your solution ought to be preferred, then.

Fred

-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

RE: COUNT Problem

[Jose Miguel Pérez]

Hi Shaun!

I beg you pardon, my last message was incomplete! I will quote the
last lines from my previous post:

---8<- Cut here ---8<---
>
>   - Thanks to the LIMIT clause, we get only the first result, which by
> the way is one of the projects with the most assigned tasks. If you want
to
> further select which one of the projects with the most assigned tasks you
> want, you must order by another column, like date (or filter out the
> projects in the WHERE clause).
> 
>   Given said that, 
>
>   Cheers,
>   Jose Miguel.
---8<- Cut here ---8<---

It was my intention to finish the message as:

"Given said that, if you want more information on how this select
works, do not hesitate to ask. ;-)"

Again, I apologize for the confusion.

Cheers,
Jose Miguel.





-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: COUNT Problem

[Chris]

Frederic Wenzel wrote:
On Sun, 17 Oct 2004 19:36:34 +, shaun thornburgh
<[EMAIL PROTECTED]> wrote:
 

A Project will have 1 or more tasks assigned to it. Using the following
query, how can I modify it so that I can find out the largest number of
tasks assigned to a group of projects.
SELECT P.*, T.*
FROM Projects P, Tasks T
WHERE P.Project_ID = T.Project_ID
AND Project_ID > 2;
   

A subselect may help:
SELECT MAX(rows) FROM (SELECT COUNT(Task_ID) AS
 rows FROM Tasks GROUP BY Project_ID) AS maxi;
Don't know ATM if it can be done more easily, but a query like this
should probably work.
Fred
 

It can be done without a sub-query:
SELECT
 COUNT(T.Project_ID) as Yourvar
FROM Projects P
LEFT JOIN Tasks T
 USING(Project_ID)
WHERE P.Project_ID > 2
GROUP BY P.Project_ID
ORDER BY COUNT(T.Project_ID) DESC
LIMIT 1
;
That *should* work, barring any typos or ommisions I may have made. I 
used LEFT JOIN because of personal preference, it can be done other way(s).

Chris
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

RE: COUNT Problem

[Jose Miguel Pérez]

Hi Shaun!

> A Project will have 1 or more tasks assigned to it. Using the 
> following 
> query, how can I modify it so that I can find out the largest 
> number of 
> tasks assigned to a group of projects.
> 
> SELECT P.*, T.*
> FROM Projects P, Tasks T
> WHERE P.Project_ID = T.Project_ID
> AND Project_ID > 2;
> 
> So if Project A has 3 tasks and Project B has 4 tasks I need 
> the query to 
> return 4.

This question is very easy to answer, but I think it's very difficult to
explain to you. For the first thing, you haven't added a GROUP BY clause,
which is the first thing you should do to begin with. It's strange you
didn't put an GROUP BY function, and if you don't know how it works, please
read a book on SQL first.

Given the tables you have, this query will do the work, without subqueries:

SELECT P.Project_ID, COUNT(T.Project_ID) as HowMany
FROM Projects P
LEFT JOIN Tasks T USING(Project_ID)
GROUP BY P.Project_ID
ORDER BY HowMany DESC
LIMIT 0,1

Column 1 (ProjecT_ID) is the Project ID with the most assigned tasks, and
Column 2 (HowMany) lists how many taks are actually asigned.

It is worth pointing out two notes about the above query:

- The query will return 0 as "HowMany" and the Project_ID for the
project which has no task assigned if this is the case. I mean, thanks to
the LEFT JOIN, we take into account projects with no taks attached. (This is
not possible with a straigh join like yours).
- Thanks to the LIMIT clause, we get only the first result, which by
the way is one of the projects with the most assigned tasks. If you want to
further select which one of the projects with the most assigned tasks you
want, you must order by another column, like date (or filter out the
projects in the WHERE clause).

Given said that, 

Cheers,
Jose Miguel.



-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: COUNT Problem

[Frederic Wenzel]

On Sun, 17 Oct 2004 19:36:34 +, shaun thornburgh
<[EMAIL PROTECTED]> wrote:
> A Project will have 1 or more tasks assigned to it. Using the following
> query, how can I modify it so that I can find out the largest number of
> tasks assigned to a group of projects.
> 
> SELECT P.*, T.*
> FROM Projects P, Tasks T
> WHERE P.Project_ID = T.Project_ID
> AND Project_ID > 2;

A subselect may help:

SELECT MAX(rows) FROM (SELECT COUNT(Task_ID) AS
  rows FROM Tasks GROUP BY Project_ID) AS maxi;

Don't know ATM if it can be done more easily, but a query like this
should probably work.

Fred

-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: COUNT Problem

[Merlin, the Mage]

Hi,

 Tihs looks to me as too few information.

 What is in your tables (the information)?

 What does the query return (a empty set)? Maybe 'cause in your where clause 
where you have Project_ID >2 you should have Project_ID=2? Or you have 
several projects with ID>2 and you want tasks for all of them?

 Or the query don't even run as Project_ID exists in both table and you should 
specify a table alias (T.Project_ID or P.Project_ID)?

themage

On Sunday 17 October 2004 20:36, shaun thornburgh wrote:
> Hi,
>
> I have the following two tables in my database:
>
> mysql> DESCRIBE Projects;
> +--+--+--+-+-++
>
> | Field| Type | Null | Key | Default | Extra  |
>
> +--+--+--+-+-++
>
> | Project_ID   | int(11)  |  | PRI | NULL| auto_increment |
> | Client_ID| int(11)  | YES  | | NULL||
> | Project_Name | varchar(100) | YES  | | NULL||
>
> +--+--+--+-+-++
> 3 rows in set (0.00 sec)
>
> mysql> DESCRIBE Tasks;
> ++-+--+-+-++
>
> | Field  | Type| Null | Key | Default | Extra  |
>
> ++-+--+-+-++
>
> | Task_ID| int(11) |  | PRI | NULL| auto_increment |
> | Task_Name  | varchar(40) |  | | ||
> | Project_ID | int(11) |  | | 0   ||
>
> ++-+--+-+-++
> 3 rows in set (0.00 sec)
>
> mysql>
>
> A Project will have 1 or more tasks assigned to it. Using the following
> query, how can I modify it so that I can find out the largest number of
> tasks assigned to a group of projects.
>
> SELECT P.*, T.*
> FROM Projects P, Tasks T
> WHERE P.Project_ID = T.Project_ID
> AND Project_ID > 2;
>
> So if Project A has 3 tasks and Project B has 4 tasks I need the query to
> return 4.
>
> Hope this makes sense!
>
> Thanks for your help

-- 
Merlin, the Mage
www.code.online.pt
www.cultodavida.online.pt

Que o(s) vosso(s) Deus(es) vos abençoe(m)
E a vida vos ame e proteja

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: Count() within Join

[SGreen]

I think what you need is a pivot table (aka: cross tab report):

SELECT c.id
, c.campaign_name
, count(1) as total
, SUM(if(a.status='optin',1,0)) as optin
, SUM(if(a.status='optout',1,0)) as optout
FROM addresses as a 
INNER JOIN addresses_incampaign as i 
on a.email_address = i.email_address
INNER JOIN campaigns as c 
on i.campaign_id = c.id 
WHERE a.user_id = "1" 
AND i.user_id = "1" 
GROUP BY c.id, c.campaign_name 
ORDER BY c.id

Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine

Scott Haneda <[EMAIL PROTECTED]> wrote on 08/06/2004 01:19:21 AM:

> Mysql 4
> 
> SELECT c.campaign_name, count(*), c.id FROM addresses as a  INNER JOIN
> addresses_incampaign as i on (a.email_address = i.email_address)  INNER 
JOIN
> campaigns as c on (i.campaign_id = c.id) WHERE a.user_id = "1" AND 
i.user_id
> = "1" GROUP BY c.id ORDER BY c.id
> 
> This gives me 
> FooNmae 100
> BarName 250
> FUD name127
> 
> Within the addresses table is a field called status, it is a enum and 
can be
> 'optin' or 'optout'.  I need to also show how many there are of each of
> those as well.  I have looked at count(a.status) but that just returns 
the
> total numbers listed above, count(DISTINCT a.status='optin') gives me 2 
for
> some reason.
> 
> Suggestions?
> -- 
> -
> Scott HanedaTel: 415.898.2602
> http://www.newgeo.com   Fax: 313.557.5052
> [EMAIL PROTECTED]Novato, CA U.S.A.
> 
> 
> 
> -- 
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
> 

Re: COUNT

[Dan Nelson]

In the last episode (May 17), Gustavo Andrade said:
> select count(distinct membros.ID) as total_membros, count(distinct
> replays.ID) as total_replays, count(distinct downloads.ID) as
> total_downloads from membros,replays,downloads;
>  
> if one of the tables have 0 records all the counts will turn to 0
> the count works only if all the tables have records
> how can i fix that?

That's a very inefficient query to start off with; it's generating
membros*replays*downloads records, then removing the dupes from each
column and counting what's left.  If you're running mysql 4.1, this
query will return data instantly (one of the very few examples of where
subselects are much better than joins):

SELECT (SELECT count(*) FROM membros) AS total_membros, 
   (SELECT count(*) FROM replays) AS total_replays, 
   (SELECT count(*) FROM downloads) AS total_downloads;

If you're running 4.0 or older, you'll need to split it up into 4
queries:

SELECT @membros:=count(*) FROM membros;
SELECT @replays:=count(*) FROM replays;
SELECT @downloads:=count(*) FROM downloads;
SELECT @membros AS total_membros, @replays AS total_replays, 
   @downloads AS total_downloads;

Discard the results of the first 3 queries.  If you're running 4.0, you
can join the first three queries into a single UNION query.

-- 
Dan Nelson
[EMAIL PROTECTED]

-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: COUNT

[John Fawcett]

From: "Gustavo Andrade"
> select count(distinct membros.ID) as total_membros, count(distinct
> replays.ID) as total_replays, count(distinct downloads.ID) as
> total_downloads from membros,replays,downloads;

Why join three tables to count the records in each one? I'm sure the
performance will be poor once you get more data.

> if one of the tables have 0 records all the counts will turn to 0
> the count works only if all the tables have records
> how can i fix that?

By joining the tables you are asking for all possible combinations of the
rows (cartesian product).
The number of rows obtained is:

(n. rows in table 1) * (n. rows in table 2) * ( n. rows in table 3)

So if a table has 0 rows there are 0 possible combinations.

For this reason and also for performance reasons, you should do 3 separate
selects.

If ID is a unique key, you can also take out the distinct, which in your
query you needed because by making all possible combinations you repeated
the same ID many times.

John

_
Quer ter um fórum para seu clan de Starcraft/BroodWar, Counter-Strike,
Warcraft ou outros. entre em
http://www.arena-star.com.br/forum/



-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: count() on multiple similar tables?

[Ken Gieselman]

Great!  Thanks for the quick pointer!
ken

Quoting Diana Soares <[EMAIL PROTECTED]>:

> You may use MERGE TABLES:
> 
> http://dev.mysql.com/doc/mysql/en/MERGE.html
> 
> -- 
> Diana Soares
> 
> On Tue, 2004-05-04 at 10:08, Ken Gieselman wrote:
> > Heya folks --
> > 
> > Trying to come up with a way to count across multiple tables, and failing
> > miserably.  I need a simple way, preferably better than looped queries, of
> > summing the number of rows in multiple tables.
> > 
> > Example:
> > 
> > I have multiple tables with the same column layout, due to the amount of
> data
> > expected to land in each one, broken up by year/month.  This is simplified
> > some, but should serve to illustrate the issue.
> > 
> > create table info_2004_03 (
> >   itemID   integer auto_increment NOT NULL,
> >   eventID  integer NOT NULL,
> >   eventNamechar(40),
> >   primary key (itemID)
> > );
> > 
> > create table info_2004_04 (
> >   itemID   integer auto_increment NOT NULL,
> >   eventID  integer NOT NULL,
> >   eventNamechar(40),
> >   primary key (itemID)
> > );
> > 
> > I need to get a total number of itemIDs over all the info_ tables.  I could
> just
> > query each of the tables individually and add the results, but looking for
> a
> > more graceful way of doing it, hopefully.
> > 
> > Thanks in advance!
> > 
> > ken
> >
> ===
> >   "Diplomacy is the weapon of the Civilized Warrior"
> > - Hun, A.T.
> >  
> > Ken Gieselman  
> [EMAIL PROTECTED]
> > System Administrator   
> http://www.endlessknot.com/~ken
> > Endlessknot Communications  
> http://www.endlessknot.com
> >
> ===
> 
> 
> 


===
  "Diplomacy is the weapon of the Civilized Warrior"
- Hun, A.T.
 
Ken Gieselman   [EMAIL PROTECTED]
System Administratorhttp://www.endlessknot.com/~ken
Endlessknot Communications   http://www.endlessknot.com
===



-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: count() on multiple similar tables?

[Diana Soares]

You may use MERGE TABLES:

http://dev.mysql.com/doc/mysql/en/MERGE.html

-- 
Diana Soares

On Tue, 2004-05-04 at 10:08, Ken Gieselman wrote:
> Heya folks --
> 
> Trying to come up with a way to count across multiple tables, and failing
> miserably.  I need a simple way, preferably better than looped queries, of
> summing the number of rows in multiple tables.
> 
> Example:
> 
> I have multiple tables with the same column layout, due to the amount of data
> expected to land in each one, broken up by year/month.  This is simplified
> some, but should serve to illustrate the issue.
> 
> create table info_2004_03 (
>   itemID   integer auto_increment NOT NULL,
>   eventID  integer NOT NULL,
>   eventNamechar(40),
>   primary key (itemID)
> );
> 
> create table info_2004_04 (
>   itemID   integer auto_increment NOT NULL,
>   eventID  integer NOT NULL,
>   eventNamechar(40),
>   primary key (itemID)
> );
> 
> I need to get a total number of itemIDs over all the info_ tables.  I could just
> query each of the tables individually and add the results, but looking for a
> more graceful way of doing it, hopefully.
> 
> Thanks in advance!
> 
> ken
> ===
>   "Diplomacy is the weapon of the Civilized Warrior"
> - Hun, A.T.
>  
> Ken Gieselman   [EMAIL PROTECTED]
> System Administratorhttp://www.endlessknot.com/~ken
> Endlessknot Communications   http://www.endlessknot.com
> ===



-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: Count all rows if limit by?

[Victor Spång Arthursson]

Thanks a lot, Jigal and Egor - just what i searched for!

Sincerely

Victor

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: Count all rows if limit by?

[Egor Egorov]

Victor Sp?ng Arthursson <[EMAIL PROTECTED]> wrote:
> Hi all!
> 
> Is there a simple way to get the total number of rows a result _should_ 
> have had if no where-clause where present? Without doing a second 
> query?
> 

If you want to get total number of rows in the table (without WHERE and without LIMIT) 
you should execute
SELECT COUNT(*) FROM table_name;

If you want to obtain number of rows the statement would have returned without the 
LIMIT clause, you can specify SQL_CALC_FOUND_ROWS keyword in the SELECT statement and 
then use FOUND_ROWS() function:
http://www.mysql.com/doc/en/Information_functions.html



-- 
For technical support contracts, goto https://order.mysql.com/?ref=ensita
This email is sponsored by Ensita.net http://www.ensita.net/
   __  ___ ___   __
  /  |/  /_ __/ __/ __ \/ /Egor Egorov
 / /|_/ / // /\ \/ /_/ / /__   [EMAIL PROTECTED]
/_/  /_/\_, /___/\___\_\___/   MySQL AB / Ensita.net
   <___/   www.mysql.com




-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: Count all rows if limit by?

[Jigal van Hemert]

From: "Victor Spång Arthursson" <[EMAIL PROTECTED]>
> Is there a simple way to get the total number of rows a result _should_
> have had if no where-clause where present? Without doing a second
> query?

Example from http://www.mysql.com/doc/en/Information_functions.html :

mysql> SELECT SQL_CALC_FOUND_ROWS * FROM tbl_name
   WHERE id > 100 LIMIT 10;
mysql> SELECT FOUND_ROWS();

Available since 4.0.0.; in versions prior to 4.1.0. this didn't work with
LIMIT 0.
Regards, Jigal.



-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: Count()

[Jacque Scott]

That works.  I knew I was missing something simple.  It was the GROUP BY
and the HAVING together.
 
Thank you very much.

>>> Michael Stassen <[EMAIL PROTECTED]> 2/25/2004 9:05:34 AM
>>>


Jacque Scott wrote:
> My program, NCR (Non-Conformity Report), keeps track of problems
with
> items that are received from vendors. I am creating a report where
the
> user can retrieve a list of vendors that have had a NCR written
against
> them a certain number of times. For example, if the user wants to
see
> what vendors have had more than 2 NCRs written against them. 
> 
> I need something like this, but it doesn't work. It gives me an
error
> saying invalid use of group function. So I put Count(Subs.Vendor) >
2
> into a HAVING clause and it return only one vendor. I know there are
> more.
> 
> SELECT Subs.Vendor
> FROM tblNCRLog INNER JOIN Subs ON tblNCRLog.SubID = Subs.SubNo 
> WHERE ((tblNCRLog.CurrentDate > '2002/10/1') AND Count(Subs.Vendor)
>
> 2);
> 
> 
> Does someone have any suggestions?
> 
> Jacque

You say you use HAVING, but it's not in your example query. Perhaps 
that's an oversight. But you're also missing a GROUP BY. Your join 
gets you one row per NCR per vendor. You need to group by vendor, then

check the count with HAVING. I think you need something like:

SELECT Subs.Vendor, COUNT(*) AS NCRs
FROM Subs, tblNCRLog
WHERE Subs.SubNo = tblNCRLog.SubID
AND tblNCRLog.CurrentDate > '2002/10/1'
GROUP BY Subs.Vendor
HAVING NCRs > 2

That would give the vendors and the counts. If you really don't want to

see the counts, so long as they're greater than 2, something like this

should do:

SELECT Subs.Vendor
FROM Subs, tblNCRLog
WHERE Subs.SubNo = tblNCRLog.SubID
AND tblNCRLog.CurrentDate > '2002/10/1'
GROUP BY Subs.Vendor
HAVING COUNT(*) > 2

Hope that helps.

Michael

Re: Count()

[Michael Stassen]

Jacque Scott wrote:
My program, NCR (Non-Conformity Report), keeps track of problems with
items that are received from vendors.  I am creating a report where the
user can retrieve a list of vendors that have had a NCR written against
them a certain number of times.  For example, if the user wants to see
what vendors have had more than 2 NCRs written against them.  
 
I need something like this, but it doesn't work.  It gives me an error
saying invalid use of group function.  So I put Count(Subs.Vendor) > 2
into a HAVING clause and it return only one vendor.  I know there are
more.
 
SELECT Subs.Vendor
FROM tblNCRLog INNER JOIN Subs ON tblNCRLog.SubID = Subs.SubNo 
WHERE ((tblNCRLog.CurrentDate > '2002/10/1') AND Count(Subs.Vendor) >
2);

 
Does someone have any suggestions?
 
Jacque
You say you use HAVING, but it's not in your example query.  Perhaps 
that's an oversight.  But you're also missing a GROUP BY.  Your join 
gets you one row per NCR per vendor.  You need to group by vendor, then 
check the count with HAVING.  I think you need something like:

  SELECT Subs.Vendor, COUNT(*) AS NCRs
  FROM Subs, tblNCRLog
  WHERE Subs.SubNo = tblNCRLog.SubID
  AND tblNCRLog.CurrentDate > '2002/10/1'
  GROUP BY Subs.Vendor
  HAVING NCRs > 2
That would give the vendors and the counts.  If you really don't want to 
see the counts, so long as they're greater than 2, something like this 
should do:

  SELECT Subs.Vendor
  FROM Subs, tblNCRLog
  WHERE Subs.SubNo = tblNCRLog.SubID
  AND tblNCRLog.CurrentDate > '2002/10/1'
  GROUP BY Subs.Vendor
  HAVING COUNT(*) > 2
Hope that helps.

Michael

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: count max days

[Colin Kettenacker]

Whenever you use the Max() function on a column while accessing other
columns at the same time you need a GROUP BY statement so that the MAX()
function knows how to "group" it's results.

I suggest you look into the manual for this. In fact it has a tutorial
showing you options on how to do exactly what you want to do:



In particular:





ck

-- 
Cheap Domain Registration | Web Hosting | Email Packages | + more
Fantastic prices -- Even better service.
http://www.hosttohost.net


[EMAIL PROTECTED] [EMAIL PROTECTED] on 10/20/03 6:52 AM wrote:

> I sent this out on last friday but I never actually saw it post to the list so
> maybe I messed up the address.  If this is showing up a second time, sorry.
> 
> I have a database where I want to count the number of days where a field gets
> to a certain value.  I am using two seperate queries to count the number of
> days that get to that value.  Right now I am using:
> 
> SELECT  count(DISTINCT aqiValues.readingDate) as greenCount
> FROMaqiRegions, aqiSites, aqiValues
> WHERE   aqiRegions.aqiRegionID = aqiSites.aqiRegionID  AND
> aqiValues.site_name = aqiSites.siteNumber AND
> aqiRegions.showOnSite = 1 AND
> aqiSites.online = 1 AND
> aqiRegions.aqiRegion = 'Twin Cities' AND
> aqiValues.readingDate >= '2003/01/01' AND
> aqiValues.aqiNumber between 0 and 51
> 
> Then I am also using:
> SELECT  count(DISTINCT aqiValues.readingDate) as yellowCount
> FROMaqiRegions, aqiSites, aqiValues
> WHERE   aqiRegions.aqiRegionID = aqiSites.aqiRegionID  AND
> aqiValues.site_name = aqiSites.siteNumber AND
> aqiRegions.showOnSite = 1 AND
> aqiSites.online = 1 AND
> aqiRegions.aqiRegion = ? AND
> aqiValues.readingDate >= '2003/01/01' AND
> aqiValues.aqiNumber between 51 and 100
> 
> My problem is I only want to count the day high value.  So if it is 25 in the
> morning and 52 in the afternoon I only want to count the 52 (the yellowcount
> query) to return results.  I have tried to work in the MAX(aqiNumber) into the
> query but I get a GROUP error.  I must be putting it in the wrong place.
> Maybe I need to somehow make this only one query?
> 
> --ja
> 
> 



-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: COUNT(*) faster?

[Paul DuBois]

At 11:40 -0500 10/6/03, Steve Buehler wrote:
I am using PHP & MySQL for a program that I am writing.  I have a 
table in my database that has a column with dates in it in the form 
-mm-dd.  Sometimes there is nothing in the table with the search 
date that I am using.  Other times there might be 1000's of rows 
with that in the date column.  I am trying to find out if there are 
any rows with the date that I am searching for in the 'date' column. 
Would the fastest way be to use
$date=2003-02-16;
SELECT COUNT(*) FROM `games` WHERE `date`='$date'
or is there a faster way?
You might try

SELECT 1 FROM `games` WHERE `date`='$date' LIMIT 1

and then see if you get a row back.  LIMIT allows MySQL to perform
some optimizations (such as terminating query processing once it finds
enough matching records).


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]


--
Paul DuBois, Senior Technical Writer
Madison, Wisconsin, USA
MySQL AB, www.mysql.com
Are you MySQL certified?  http://www.mysql.com/certification/

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: COUNT(*) faster?

[Director General: NEFACOMP]

I think this is OK.
But if you want to get a recordset with data and know how much records you
have with only ONE query,

you may use:
$date=2003-02-16;
SELECT col1, col2, coln  FROM `games` WHERE `date`='$date'
$num_rows=0;
$num_rows=mysql_num_rows($rs_resource_identifier);

I don't remember exactly

Try it.

Thanks
Emery
- Original Message -
From: "Steve Buehler" <[EMAIL PROTECTED]>
To: "mysql" <[EMAIL PROTECTED]>
Sent: Monday, October 06, 2003 18:40
Subject: COUNT(*) faster?


> I am using PHP & MySQL for a program that I am writing.  I have a table in
> my database that has a column withut dates in it in the form
> -mm-dd.  Sometimes there is nothing in the table with the search date
> that I am using.  Other times there might be 1000's of rows with that in
> the date column.  I am trying to find out if there are any rows with the
> date that I am searching for in the 'date' column.  Would the fastest way
> be to use
> $date=2003-02-16;
> SELECT COUNT(*) FROM `games` WHERE `date`='$date'
> or is there a faster way?
>
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
>
>
>



-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: Count Rows?

[Jeremy Zawodny]

On Wed, Jul 02, 2003 at 10:57:18AM -0500, Roy W wrote:
> Is there a simple MySQL command that will give a Row Count (# of records)
> WITHOUT running a select (huge database)

If it's a MyISAM table, just run a SELECT COUNT(*) FROM table_name.
It's really efficient.  Try it. :-)
-- 
Jeremy D. Zawodny |  Perl, Web, MySQL, Linux Magazine, Yahoo!
<[EMAIL PROTECTED]>  |  http://jeremy.zawodny.com/

MySQL 4.0.13: up 31 days, processed 979,861,677 queries (357/sec. avg)

-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

RE: Count Rows?

[Mike Hillyer]

If your table is MyISAM, then 

SELECT COUNT(*) FROM tablename

Will return a rowcount without a major performance hit as the rowcount
is stored and a table scan is not needed.

Regards,
Mike Hillyer
www.vbmysql.com


> -Original Message-
> From: Roy W [mailto:[EMAIL PROTECTED] 
> Sent: Wednesday, July 02, 2003 9:57 AM
> To: [EMAIL PROTECTED]
> Subject: Count Rows?
> 
> 
> Is there a simple MySQL command that will give a Row Count (# 
> of records)
> WITHOUT running a select (huge database)
>  
> Thanks!
>  
> Roy
>  
> 

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

RE: Count on Multiple Tables

[Mike Hillyer]

SELECT DATE_FORMAT(clicks.affiliate_clientdate, '%M %D, %Y') AS date,
COUNT(affiliate_clickthrough_id) AS hits, count(affiliate_orders_id) AS
sells FROM affiliate_clickthroughs AS clicks LEFT JOIN
affiliate_stores_sales as sales USING(affiliate_id) WHERE
clicks.affiliate_id = '111' AND
clicks.affiliate_stores_id = '123' AND
MONTH(clicks.affiliate_clientdate) = '06' AND
YEAR(clicks.affiliate_clientdate) = '2003' GROUP BY date;


Does that do what you are looking for?

Regards,
Mike Hillyer
www.vbmysql.com

-Original Message-
From: Ralph [mailto:[EMAIL PROTECTED] 
Sent: Thursday, June 05, 2003 10:33 AM
To: Mike Hillyer; [EMAIL PROTECTED]
Subject: RE: Count on Multiple Tables



CREATE TABLE `affiliate_clickthroughs` (
  `affiliate_clickthrough_id` int(11) NOT NULL auto_increment,
  `affiliate_id` int(11) NOT NULL default '0',
  `affiliate_stores_id` bigint(11) NOT NULL default '0',
  `affiliate_clientdate` datetime NOT NULL default '-00-00
00:00:00',
  `affiliate_clientbrowser` varchar(200) default 'Could Not Find This
Data',
  `affiliate_clientip` varchar(50) default 'Could Not Find This Data',
  `affiliate_clientreferer` varchar(200) default 'none detected (maybe a
direct link)',
  `affiliate_products_id` int(11) default '0',
  `affiliate_banner_id` int(11) NOT NULL default '0',
  PRIMARY KEY  (`affiliate_clickthrough_id`),
  KEY `refid` (`affiliate_id`)
) TYPE=MyISAM AUTO_INCREMENT=9 ;


CREATE TABLE `affiliate_stores_sales` (
  `affiliate_id` int(11) NOT NULL default '0',
  `affiliate_stores_id` bigint(11) NOT NULL default '0',
  `affiliate_orders_id` int(11) NOT NULL default '0',
  `affiliate_clickthroughs_id` int(11) NOT NULL default '0',
  `affiliate_payment_id` int(11) NOT NULL default '0',
  `date` datetime NOT NULL default '-00-00 00:00:00',
  `browser` varchar(100) NOT NULL default '',
  `ipaddress` varchar(20) NOT NULL default '',
  `sale` decimal(15,2) NOT NULL default '0.00',
  `commission` decimal(15,2) NOT NULL default '0.00',
  `billing_status` int(5) NOT NULL default '0',
  `payment_date` datetime NOT NULL default '-00-00 00:00:00',
  `commission_percent` decimal(4,2) NOT NULL default '0.00',
  PRIMARY KEY  (`affiliate_orders_id`)
) TYPE=MyISAM;

-Original Message-
From: Mike Hillyer [mailto:[EMAIL PROTECTED] 
Sent: Thursday, June 05, 2003 7:09 AM
To: Ralph; [EMAIL PROTECTED]
Subject: RE: Count on Multiple Tables

Can you show some table structure so we have something work with? It's
hard to recommend a query when we do not know what your sales table
structure is.

Regards,
Mike Hillyer
www.vbmysql.com


-Original Message-
From: Ralph [mailto:[EMAIL PROTECTED] 
Sent: Wednesday, June 04, 2003 4:00 PM
To: [EMAIL PROTECTED]
Subject: Count on Multiple Tables


I've been stuck on this one all morning. Can't seem to figure it out.

I have 2 tables, one with affiliate sales and another with affiliate
clickthroughs. I have to query both tables, so that I can get
clickthrough dates, hits, and then query affiliate sales table to get
number of orders for each date. I want to display the results like this:

DATE  | TOTAL HITS | TOTAL SALES
05/03/2003   6   1
05/04/2003   7   0

 
I've managed to get dates and total hits by using the following query:

SELECT DATE_FORMAT(affiliate_clientdate, '%M %D, %Y') AS date, COUNT(*)
AS hits FROM affiliate_clickthroughs WHERE affiliate_id = '111' AND
affiliate_stores_id = '123' AND MONTH(affiliate_clientdate) = '06' AND
YEAR(affiliate_clientdate) = '2003' GROUP BY date";

But then I can't get the total number of sales on affiliate sales table.

Any suggestions? Your help is greatly appreciated.



-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]




--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

RE: Count on Multiple Tables

[Ralph]

CREATE TABLE `affiliate_clickthroughs` (
  `affiliate_clickthrough_id` int(11) NOT NULL auto_increment,
  `affiliate_id` int(11) NOT NULL default '0',
  `affiliate_stores_id` bigint(11) NOT NULL default '0',
  `affiliate_clientdate` datetime NOT NULL default '-00-00
00:00:00',
  `affiliate_clientbrowser` varchar(200) default 'Could Not Find This
Data',
  `affiliate_clientip` varchar(50) default 'Could Not Find This Data',
  `affiliate_clientreferer` varchar(200) default 'none detected (maybe a
direct link)',
  `affiliate_products_id` int(11) default '0',
  `affiliate_banner_id` int(11) NOT NULL default '0',
  PRIMARY KEY  (`affiliate_clickthrough_id`),
  KEY `refid` (`affiliate_id`)
) TYPE=MyISAM AUTO_INCREMENT=9 ;


CREATE TABLE `affiliate_stores_sales` (
  `affiliate_id` int(11) NOT NULL default '0',
  `affiliate_stores_id` bigint(11) NOT NULL default '0',
  `affiliate_orders_id` int(11) NOT NULL default '0',
  `affiliate_clickthroughs_id` int(11) NOT NULL default '0',
  `affiliate_payment_id` int(11) NOT NULL default '0',
  `date` datetime NOT NULL default '-00-00 00:00:00',
  `browser` varchar(100) NOT NULL default '',
  `ipaddress` varchar(20) NOT NULL default '',
  `sale` decimal(15,2) NOT NULL default '0.00',
  `commission` decimal(15,2) NOT NULL default '0.00',
  `billing_status` int(5) NOT NULL default '0',
  `payment_date` datetime NOT NULL default '-00-00 00:00:00',
  `commission_percent` decimal(4,2) NOT NULL default '0.00',
  PRIMARY KEY  (`affiliate_orders_id`)
) TYPE=MyISAM;

-Original Message-
From: Mike Hillyer [mailto:[EMAIL PROTECTED] 
Sent: Thursday, June 05, 2003 7:09 AM
To: Ralph; [EMAIL PROTECTED]
Subject: RE: Count on Multiple Tables

Can you show some table structure so we have something work with? It's
hard to recommend a query when we do not know what your sales table
structure is.

Regards,
Mike Hillyer
www.vbmysql.com


-Original Message-
From: Ralph [mailto:[EMAIL PROTECTED] 
Sent: Wednesday, June 04, 2003 4:00 PM
To: [EMAIL PROTECTED]
Subject: Count on Multiple Tables


I've been stuck on this one all morning. Can't seem to figure it out.

I have 2 tables, one with affiliate sales and another with affiliate
clickthroughs. I have to query both tables, so that I can get
clickthrough dates, hits, and then query affiliate sales table to get
number of orders for each date. I want to display the results like this:

DATE  | TOTAL HITS | TOTAL SALES
05/03/2003   6   1
05/04/2003   7   0

 
I've managed to get dates and total hits by using the following query:

SELECT DATE_FORMAT(affiliate_clientdate, '%M %D, %Y') AS date, COUNT(*)
AS hits FROM affiliate_clickthroughs WHERE affiliate_id = '111' AND
affiliate_stores_id = '123' AND MONTH(affiliate_clientdate) = '06' AND
YEAR(affiliate_clientdate) = '2003' GROUP BY date";

But then I can't get the total number of sales on affiliate sales table.

Any suggestions? Your help is greatly appreciated.



-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]




-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

RE: Count on Multiple Tables

[Mike Hillyer]

Can you show some table structure so we have something work with? It's
hard to recommend a query when we do not know what your sales table
structure is.

Regards,
Mike Hillyer
www.vbmysql.com


-Original Message-
From: Ralph [mailto:[EMAIL PROTECTED] 
Sent: Wednesday, June 04, 2003 4:00 PM
To: [EMAIL PROTECTED]
Subject: Count on Multiple Tables


I've been stuck on this one all morning. Can't seem to figure it out.

I have 2 tables, one with affiliate sales and another with affiliate
clickthroughs. I have to query both tables, so that I can get
clickthrough dates, hits, and then query affiliate sales table to get
number of orders for each date. I want to display the results like this:

DATE  | TOTAL HITS | TOTAL SALES
05/03/2003   6   1
05/04/2003   7   0

 
I've managed to get dates and total hits by using the following query:

SELECT DATE_FORMAT(affiliate_clientdate, '%M %D, %Y') AS date, COUNT(*)
AS hits FROM affiliate_clickthroughs WHERE affiliate_id = '111' AND
affiliate_stores_id = '123' AND MONTH(affiliate_clientdate) = '06' AND
YEAR(affiliate_clientdate) = '2003' GROUP BY date";

But then I can't get the total number of sales on affiliate sales table.

Any suggestions? Your help is greatly appreciated.



-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: COUNT(DISTINCT ...)

[Paul DuBois]

At 16:42 -0500 3/12/03, Bob Sawyer wrote:
HELP! I'm getting the following error:

--
Error (SQL):
SELECT date, subject, location, private, id, duration, dategroup_id,
COUNT(DISTINCT subject) AS appointment_count, inituserid FROM mgw_calendar
WHERE userid=1 AND SUBSTRING(date,1,8) = '20030312' GROUP BY date, subject,
location, private, id, duration, dategroup_id, inituserid ORDER BY date
Error (MSG):
You have an error in your SQL syntax near 'DISTINCT subject) AS
appointment_count, inituserid FROM mgw_calendar WHERE useri' at line 1
---
I'm using MySQL 3.22.32  does COUNT(DISTINCT ...) not work with this
version?
No, not until 3.23.2.

Thanks,

Bob Sawyer
---
Pixellated!
Design:Develop:Deliver
www.pixellated.org


-
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