mysql count

[hyster]

im trying to do  count from  col1 only if col2 = pass

this is what ive tried so far. the 1st query i listed is the only 1 that dosent show an error but it only displays col1 and no count.

if i remove the part about col2 it works ok.

any suggestions?

 

$query = "SELECT make, COUNT(make) and comments = 'pass' FROM $tbl_name GROUP BY make  order by count(make) desc";  

$query = "SELECT make, COUNT(make) and comments like 'pass' FROM $tbl_name GROUP BY make  order by count(make) desc";  

$query = "SELECT make, COUNT(make) where comments like 'pass' FROM $tbl_name GROUP BY make  order by count(make) desc";  

 

also is it possably to search only for an empty field? ie i have a col that only contains pass or it is empty. id like to search for only the rows that contain an empty field.

[ManiacDan]

you want a WHERE clause.  And don't select make, count(make), just count(make).

[hyster]

after a few hours of messing i finaly got this working using

 

SELECT make,COUNT(make) from dsgi_serval where comments like 'pass' GROUP BY make order by count(make) desc

 

 

my next trick is to get a full count next to a where=count

IE:

10 rows in make and only 6 are 'pass' so return would be "make = 6/10".

 

do i have to nest the query do do this or is there another way? (i have never nested a query before)

[ManiacDan]

SELECT 
  SUM ( IF ( comments = 'pass', 1, 0 ) ) AS withComments,
  SUM(1) AS total
FROM
  dsgi_serval
ORDER BY 
  SUM ( IF ( comments = 'pass', 1, 0 ) )  DESC

-Dan

[hyster]

i used  (some statments were missing spaces between them)

 

SELECT   
     SUM ( IF ( comments = 'pass', 1, 0 ) ) AS with Comments,
     SUM(1) AS total 
FROM  
   dsgi_serval 
ORDER BY 
   SUM ( IF ( comments = 'pass', 1, 0 ) )  DESC

 

and it came back with this error

 

 """"""""""   right syntax to use near 'with Comments,
     SUM(1) AS total 
FROM  
   dsgi_serval 
ORDER BY    SU' at line 2 

 

ive never seen a query structered like this and i cannot pick much of it out.

 

[mikosiko]

(some statments were missing spaces between them)

 

funny... but is ok...

 

this:

AS withComments,

 

is not the same as:

AS with Comments,

[hyster]

the reason i put the space is that it returns "exel.SUM does not exist " with the space so i thought it was an typo.

 

i used ur code un-alted and i got this

use near 'dsgi_servalORDER BY   SUM ( IF ( comments = 'pass', 1, 0 ) )  DESC' at line 1 

 

this caught my eye 'dsgi_servalORDER BY so i went through it for other bits that "looked" wrong.

 

i really appreciate your help in this.

[mikosiko]

post the complete sentence that you have now

[hyster]

this is what i have that works.

im running it in phpmyadmin atm but ill add the php later.

 

SELECT make,COUNT(make) from dsgi_serval where comments like 'pass' GROUP BY make order by count(make) desc

 

im trying to get the full row count then the row count that contains "pass", so the end result would be.

 

20 rows in total, 15 contain "pass"

 

"make = 15/20" or "make = 20/15"

 

the sum section i cannot get working.

 

 

thanks

 

 

[mikosiko]

post the sentence that was giving to you by Dan showing exactly how you have it right now (the one that is giving you errors)....

[hyster]

SELECT   
SUM ( IF ( comments = 'pass', 1, 0 ) ) AS withComments,  
SUM(1) AS totalFROM  
dsgi_servalORDER BY   
SUM ( IF ( comments = 'pass', 1, 0 ) )  DESC

 

error

 

#1064 - You have an error in your SQL syntax; check the manual 
that corresponds to your MySQL server version for the right syntax 
to use near 'dsgi_servalORDER BY   SUM ( IF ( comments = 'pass', 1, 0 ) )  DESC' at line 1 

[mikosiko]

my friend.... you have some spaces where you don't have to  (note the spaces after SUM... those are wrong).. and you need spaces in others places (before FROM and ORDER)

 

try:

SELECT SUM( IF ( comments = 'pass', 1, 0 ) ) AS withComments,  
       SUM(1) AS total
  FROM  dsgi_serval
ORDER BY SUM( IF ( comments = 'pass', 1, 0 ) )  DESC;

 

and even when that statement is correct, I personally I will write it like this, considering that you want to have those counts grouped by make (as you posted in your first SQL):

SELECT  make,
        COUNT(*) AS total,
        SUM(IF(comments = 'pass', 1, 0)) AS withComments 
  FROM  dsgi_serval
  GROUP BY make
  ORDER BY COUNT(*)  DESC;

 

 

notice that for "total" I did use COUNT only because SUM and COUNT perform a little different... but both will have the same results for that specific field

[hyster]

thanks miko, that works perfect.