CODEWITHSUNDEEP
phpmysqlsql
Mason
Mason

Reputation: 157

Mysql how to use SUM and COUNT in the same query

I have the query below, which is supposed to get all the "record" fields from a mysql table called users. The record field's values must be bigger than 0 for it to count. and the query only returns true if 3 or more records found (where record > 0)

The query below makes sense to me, but its returning the following PHP error : Operand should contain 2 column(s)

$query = "
SELECT * FROM users u
WHERE (

    SELECT COUNT(record) AS record,

    SUM(CASE WHEN record > 0 THEN 1 ELSE 0 END)

    FROM users

) >= 3

";

Can SUM and COUNT not be used in the same query? I've used them simultaneously in the past with no problems.

Any help would be great thank

EDIT ---------------------

Table : users 
--------------
id       value
--------------
 1        1
 2        1
 3        1
 4        0
 5        0
 6        -1
 7        -10
 8        0

I'd like to only return a result if the value field in the table above is bigger than 0. But I also only want to return a result if the total number of values found in the table (where value > 0) are 3 or more.

Upvotes: 1

Views: 1739

Answers (1)

user3783243
user3783243

Reputation: 5224

You can just use the count function to count, a where to limit the data, and the having function to check that you have the number of records you want.

select count(*) as counted 
from users 
where record > 0 
having counted > 3

Demo: http://sqlfiddle.com/#!9/29ed41e/1

With the above query your PHP will have the results as the first index, or counted depending on how you fetch. You don't need to loop the fetch because there will only be 1 row returned.

Roughly:

$row = $query->fetch();
return $row['counted'];

The number of rows will be 1 so you don't want to count the number of rows, you want the actual returned value.

Upvotes: 1

Related Questions