I have this simple MYSQL table1:
id
1
2
3
AND this table2:
id | question | answer
1 | how |
2 | are | fine
3 | you | ok
And this simple query:
SELECT table1.id,COUNT(answer NOT NULL) FROM table1 LEFT JOIN table2
ON table1.id = table2.id
I want to count non empty answer fields. I tried NOT NULL but it's not working as the answer column is NOT NULL and where is not applicable as it will lead the main query to return nothing
You can use CHAR_LENGTH(str) function to check length of value. Use below query :
SELECT COUNT(answer) FROM table WHERE CHAR_LENGTH(answer)>0;
From Reference Doc.
EDIT :
You may try this query :
SELECT COUNT(answer) FROM table1 LEFT JOIN table2
ON table1.id = table2.id AND CHAR_LENGTH(table2.answer)>0
You could try this, to capture everything that is blank:
SELECT COUNT(id) FROM table
where answer IS NOT NULL or answer <>''
That should grab and count any row that has any value in "answer".
Or, if there is a join:
SELECT table1.count(id), table2.question
FROM table1 LEFT OUTER JOIN
tabel1 ON table1.id = table2.id
GROUP BY table1.id,table2.question
HAVING (table2.answer <> '') OR (table2.answer IS NOT NULL)
Just count Number of rows having NULL answers!
SELECT COUNT(1) FROM table /* COUNT(1) will go on counting rows frequncy*/
where answer IS NOT NULL
If the answer column is not nullable then IS NOT NULL will return all the rows. What you want is "is not empty string":
SELECT COUNT(*)
FROM table
WHERE answer <> "";
If answer is nullable then you can check for both conditions:
SELECT COUNT(*)
FROM table
WHERE answer IS NOT NULL AND answer <> "";
SELECT COUNT(t2.answer) count
FROM table1 t1
LEFT JOIN table2 t2
ON t2.id = t1.id
AND t2.answer != ''
OR
SELECT t1.id, COUNT(t2.answer) count
FROM table1 t1
LEFT JOIN table2 t2
ON t2.id = t1.id
AND t2.answer != ''
GROUP BY t1.id
You can use this SUM(CHAR_LENGTH((answer))/CHAR_LENGTH((answer))) formula to count non empty answer fields.
SELECT table1.id, SUM(CHAR_LENGTH((answer))/CHAR_LENGTH((answer))) FROM table1
LEFT JOIN table2 ON table1.id = table2.id
Count each of your columns:
SELECT count(`id`) + count(`personal_id`) + count(`f_name`) + ...
FROM `detail_members` WHERE `personal_id` = '$personalid'
