CODEWITHSUNDEEP
phpmysqlcount
user317005
user317005

Reputation:

MySQL, count the number of "filled" fields in a table

MySQL (table):

+----+------+
| id | text |
+----+------+
| 1  |      |
+----+------+
| 2  | blah |
+----+------+
| 3  |      |
+----+------+
| 4  | blah |
+----+------+
| 5  | blah |
+----+------+

PHP:

$a = mysql_query("SELECT COUNT(*) AS count1 FROM `table`");
$b = mysql_fetch_assoc($a);

echo $b['count1'];

Output:

5

However, I also want to count the text fields which are filled - within the same query, if possible.

Result:

5 in total
3 with filled text fields

Upvotes: 3

Views: 2170

Answers (2)

Charles Ray
Charles Ray

Reputation: 480

This can be accomplished quite nicely with sub-queries.

SELECT COUNT(id) AS id, COUNT(SELECT text FROM 'table' WHERE text IS NOT NULL) AS t FROM 'table'

Note to self: start proofreading your work before submitting it.

Upvotes: 4

Linus Kleen
Linus Kleen

Reputation: 34642

SELECT COUNT(*) AS `total`, SUM(IF(`text` <> "",1,0)) AS `non_empty` FROM `table`

Upvotes: 10

Related Questions