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 - http://php.net/manual/en/pdostatement.rowcount.php — WillardSolutions, Apr 19 '17 at 16:03

score 4 · Answer 1 · answered Apr 19 '17 at 16:02

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 '17 at 16:02

Sloan Thrasher

4,953
3
22
40

Thank you. I tested it. Just cannot get from there to 3/9. I get something like 0101010101 – peace_love Apr 19 '17 at 16:21
You're probably echoing the object itself, not the results. You need to fetch the results. – WillardSolutions Apr 19 '17 at 16:23

Lee · Accepted Answer · 2017-04-19T16:24:12.853

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 '17 at 16:24

answered Apr 19 '17 at 16:08

Lee

10,496
4
37
45

Looks good. How do I execute the result? `$result = $pdo->fetchAll();` and then `foreach ($result as $row) { echo $row['totalCount'];}`? – peace_love Apr 19 '17 at 16:10
I tested it, but not getting the result. I must make a mistake somewhere. Let you know if I solved it – peace_love Apr 19 '17 at 16:23
@Jarla added an example – Lee Apr 19 '17 at 16:24

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

2 Answers2