Mysql how to use SUM and COUNT in the same query

Question

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.

I have not upgraded, still on the previous version. It works fine :). the only thing not working is the SUM part of the query — Mason
– Mason, Commented Jun 28, 2018 at 3:39
Count and Sum can definitely be used in the same query. You are getting the error because your WHERE clause is trying to compare a two-column result to a single value (>=3). What you probably need here is a join, but I'm unclear what you are really trying to do. Please post a sampling of your data, including all columns, and I can get it for you. — SeanW333
– SeanW333, Commented Jun 28, 2018 at 3:54
I think the best answer is by @user3783243. I just wanted to use SUM to return how many values are found in the database as a solid number, rather than going through foreach loops in the php code. — Mason
– Mason, Commented Jun 28, 2018 at 4:03
Yes @user3783243 your query does it best! thanks a lot. Really helpful. Is there a way to return the number of counted records for the query without doing a foreach look in php — Mason
– Mason, Commented Jun 28, 2018 at 4:04

user3783243 · Accepted Answer · 2018-06-28 04:21:48Z

1

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.

edited Jun 28, 2018 at 4:21

answered Jun 28, 2018 at 4:06

user3783243

5,2125 gold badges27 silver badges45 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Mason Over a year ago

Thanks makes sense. Great solution. So I could just use something like return $query->num_rows(); to get the total count?

user3783243 Over a year ago

No, there will only be 1 row returned. Fetch the row and return the value it has.

Collectives™ on Stack Overflow

Mysql how to use SUM and COUNT in the same query

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related