Re: count(expr)

[Paul DuBois]

On Thu, Jan 09, 2003 at 12:24:28PM -0600, Paul DuBois wrote:
...

 You probably want SUM(col3  col4,1,0) instead.


  Hey Paul,

  Am I missing something here?

  I did not think that SUM(col3  col4,1,0) would be valid syntax?


  Cheers!
--
 Zak Greant [EMAIL PROTECTED] | MySQL Advocate |  http://zak.fooassociates.com


Zak's correct.  I erred.  The expression would use IF() inside a SUM():

SUM(IF(col3  col4,1,0))


Sheesh.

-
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

count(expr)

[Meena Vyavahare]

Here is my query-

Select col1, col2, count(*) as total, count(col3  col4) as violations
from table1
Where condn
group by col1, col2

The result of this query shows same values at both the columns for total
and violations.
The count(expr) does not work

Thanks

Meena
---


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

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

Re: count(expr)

[Keith C. Ivey]

On 9 Jan 2003, at 10:29, Meena Vyavahare wrote:

 Select col1, col2, count(*) as total, count(col3  col4) as violations
 from table1
 Where condn
 group by col1, col2
 
 The result of this query shows same values at both the columns for total
 and violations.
 The count(expr) does not work

You're misunderstanding how COUNT() works.  Since the expression 
(col3  col4) is never null, counting it is the same as counting the 
number of rows.  See the documentation:

   http://www.mysql.com/doc/en/Group_by_functions.html

In this situation you might use SUM() instead, since the inequality 
evaluates to either 0 or 1 in numeric context.

-- 
Keith C. Ivey [EMAIL PROTECTED]
Tobacco Documents Online
http://tobaccodocuments.org
Phone 202-667-6653

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

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

Re: count(expr)

[Paul DuBois]

At 10:29 -0600 1/9/03, Meena Vyavahare wrote:

Here is my query-

Select col1, col2, count(*) as total, count(col3  col4) as violations
from table1
Where condn
group by col1, col2

The result of this query shows same values at both the columns for total
and violations.
The count(expr) does not work


Sure it does.  It's counting non-NULL values.  If no values in col3 or
col4 are NULL, then col3  col4 evaluates to either 0 or 1 and is
counted, and your total will be the same as COUNT(*).

You probably want SUM(col3  col4,1,0) instead.



Thanks

Meena
---



-
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