How can I count different mysql rows in one database request?

Question

$pdo = $db->query('SELECT * FROM data ;');
$total = $pdo->rowCount();  
echo $total;

The result is for example 3.

But I have a column named "done" in mySQL database where the possible value is 1 or 0.

I want now to count all rows with the value 1. So if there are for example in total 9 elements in the database and from them three items with the value 1 then the result should be:

3/9

I know only know how to do this with a second database request

 $pdo = $db->query('SELECT * FROM data WHERE done = "1"  ;');
 $done = $pdo->rowCount();  

 echo $done."/".$total;

But I was wondering if this is possible in just one database request.

Careful , rowcount() is not consistent across all databases for SELECT statements - php.net/manual/en/pdostatement.rowcount.php — WillardSolutions
– WillardSolutions, Commented Apr 19, 2017 at 16:03

Sloan Thrasher · Accepted Answer · 2017-04-19 16:02:39Z

4

Try this for the query, and get the info from the results (not row count)

SELECT `done`, count(*) as `numrows` FROM `data` GROUP BY `done`;

answered Apr 19, 2017 at 16:02

Sloan Thrasher

5,0383 gold badges26 silver badges44 bronze badges

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

2 Comments

peace_love Over a year ago

Thank you. I tested it. Just cannot get from there to 3/9. I get something like 0101010101

WillardSolutions Over a year ago

You're probably echoing the object itself, not the results. You need to fetch the results.

Lee · Accepted Answer · 2017-04-19 16:24:12Z

1

MySQL supports expressions in SUM, so if your looking to get a total count and a filtered count at the same time you can do this:

SELECT COUNT(*) as totalCount, SUM(`done`=1) as completeCount FROM `data`

You can also add in the "not done" count if needed:

SELECT COUNT(*) as totalCount, SUM(`done`=1) as completeCount, SUM(`done`=0) as incompleteCount FROM `data`

With your code:

$query = 'SELECT COUNT(*) as total, SUM(`done`=1) as complete FROM `data`';
$pdo = $db->query($query);
$data = $pdo->fetch(); // Uses PDO::FETCH_BOTH by default  
echo $data['complete']."/".$data['total'];

edited Apr 19, 2017 at 16:24

answered Apr 19, 2017 at 16:08

Lee

10.6k4 gold badges39 silver badges45 bronze badges

3 Comments

peace_love Over a year ago

Looks good. How do I execute the result? $result = $pdo->fetchAll(); and then foreach ($result as $row) { echo $row['totalCount'];}?

peace_love Over a year ago

I tested it, but not getting the result. I must make a mistake somewhere. Let you know if I solved it

Lee Over a year ago

@Jarla added an example

Collectives™ on Stack Overflow

How can I count different mysql rows in one database request?

2 Answers 2

2 Comments

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related