Find the Number of columns with the same value

Question

+-----+-----+----+----+----+----+----+----+
| Uid | Q1 |  Q2 | Q3 | Q4 | Q5 | Q6 | Q7 |
+=====+=====+====+====+====+====+====+====+
|  1  |  1  |  0 |  1 |  0 | 1  |  1 |  0 |
+-----+-----+----+----+----+----+----+----+

This is a structure of my database. I want to fetch number of columns where uid = $_SESSION['Uid']. I want Numbers of columns where 0 is present in between Q1, Q2, Q5, Q7 something like this, (not a query its just for reference)

$Uid = $_SESSION['Uid'];
$result = mysql_query("SELECT Q1, Q2, Q5, Q7 FROM userdata WHERE Uid = '$Uid' && have 0");
echo mysql_num_fields($result);

so it will return 2 or this can be solve by using count function. Or how should I transform or create a new table like this after selecting all fields for that particular user?

+----+----+
|  Q |  A |
+====+====+
| Q1 |  1 |
+====+====+
| Q2 |  0 |
+====+====+
| Q3 |  1 |
+====+====+
| Q4 |  0 |
+====+====+
| Q5 |  1 |
+====+====+
| Q6 |  1 |
+====+====+
| Q7 |  0 |
+====+====+

***Please [stop using `mysql_*` functions](http://stackoverflow.com/questions/12859942/why-shouldnt-i-use-mysql-functions-in-php).*** [These extensions](http://php.net/manual/en/migration70.removed-exts-sapis.php) have been removed in PHP 7. Learn about [prepared](http://en.wikipedia.org/wiki/Prepared_statement) statements for [PDO](http://php.net/manual/en/pdo.prepared-statements.php) and [MySQLi](http://php.net/manual/en/mysqli.quickstart.prepared-statements.php) and consider using PDO, [it's really pretty easy](http://jayblanchard.net/demystifying_php_pdo.html). — Jay Blanchard, Feb 12 '18 at 20:50

score 1 · Accepted Answer · answered Feb 12 '18 at 21:03

1

You could sum the results of IF :

SELECT IF(Q1=0,1,0) + IF(Q2=0,1,0) + IF(Q5=0,1,0) + IF(Q7=0,1,0) as total
FROM userdata WHERE Uid = '$Uid';

Outputs:

+-------+
| total |
+-------+
|     2 |
+-------+

answered Feb 12 '18 at 21:03

Syscall

19,327
10
37
52

it does not work either. It gives me 1 instead of 2. – Ameya Feb 12 '18 at 21:20
@Ameya I've tested with the record in your question and give me 2. On which data have you made this test please ? – Syscall Feb 12 '18 at 21:24
@Ameya This query counts the number of columns in Q1,Q2,Q5 and Q7 that having a zero value. – Syscall Feb 12 '18 at 21:26
@Ameya Did you find a way in PHP to get the results? – Syscall Feb 12 '18 at 21:40
yeah, it did work thanks and what if I want Q with 1 value? should I invert this like, `IF(Q1=1,0,1)`? – Ameya Feb 12 '18 at 21:41
@Ameya `IF (column=value_you_want, 1, 0)`, to count the columns with the value you want. – Syscall Feb 12 '18 at 21:44

score 0 · Answer 2 · answered Feb 12 '18 at 20:47

0

You should check that each field = 0

SELECT Q1, Q2, Q5, Q7 
FROM userdata WHERE Uid = '$Uid' AND (Q1=0 OR Q2=0 OR Q5=0 OR Q7=0)

answered Feb 12 '18 at 20:47

McNets

10,352
3
32
61

I used this command previously but it gives me result 4 which is the total number of fields I am selecting regardless it contains 0 no not. – Ameya Feb 12 '18 at 21:05

Barmar · Answer 3 · 2018-02-12T21:27:44.247

0

You can use the NOT operator to invert a true/false value (which is represented in MySQL as 1/0), so:

SELECT (NOT q1) + (NOT q2) + (NOT q5) + (NOT q7) AS total
FROM userdata
WHERE Uid = $Uid

DEMO

If you prefer to think of them as numbers rather than truth values, you could use (1 - q1), etc.

edited Feb 12 '18 at 21:27

answered Feb 12 '18 at 21:26

Barmar

741,623
53
500
612

That's give the same results of my anwser, but more readable :) – Syscall Feb 12 '18 at 21:27
@Syscall I know, just another way to write it. Also `(1 - q1)`. – Barmar Feb 12 '18 at 21:28

score -1 · Answer 4 · answered Feb 12 '18 at 20:51

-1

You can UNPIVOT your table and query it as well.

this link describes unpivoting in mysql. this is especially usefull if your table columns are dynamic and/or if you need to generate complex reports.

answered Feb 12 '18 at 20:51

Derviş Kayımbaşıoğlu

28,492
4
50
72

While this link may answer the question, it is better to include the essential parts of the answer here and provide the link for reference. Link-only answers can become invalid if the linked page changes. – Jay Blanchard Feb 12 '18 at 21:00

Find the Number of columns with the same value

4 Answers4