Count same values in different column mysql

Question

I need to count how many times in ripeted the same values in different columns for the same id.. I'll try to clarify with an example: TABLE:

+-----+-----+-----+-----+-----+
|  id | d01 | d02 | d03 | d04 |
+=====+=====+=====+=====+=====+
|  1  |  A  |  A  | B   |  B  | 
+-----+-----+-----+-----+-----+
|  2  |  A  |  A  |  A  |  A  | 
+-----+-----+-----+-----+-----+
|  3  |  B  |  B  |  A  |  A  | 
+-----+-----+-----+-----+-----+
|  4  |  A  |  A  |  A  |  A  | 
+-----+-----+-----+-----+-----+
|  5  |  A  |  A  |  A  |  A  | 
+-----+-----+-----+-----+-----+
|  6  |  B  |  A  |  A  |  A  | 
+-----+-----+-----+-----+-----+

I need to know how many times the value "B" is repeating for any person (ID)..

Is that possible to do that? RESULTS

+-----+-----+-----+
|  id |  count B  |
+=====+=====+=====+
|  1  |     2     |
+-----+-----+-----+
|  2  |     0     |
+-----+-----+-----+
|  3  |     2     |
+-----+-----+-----+

I was thinking to use the function "SUM" but I have no idea how to display just the single ID. Thanks in advance, hope the question is clear enough!

'select userid , sum(case when d01 = 'AG' then 1 else 0 end + case when d02 = 'AG' then 1 else 0 end + case when d03 = 'AG' then 1 else 0 end + ) as SumOfNines from gennaio group by userid' But is not working — diego, Feb 24 '14 at 03:49
How about getting the whole information into a PHP array and using PHP array functions for this? — AyB, Feb 24 '14 at 05:05
@diego Which mysql version do you use? mysql or mysqli or PDO? — AyB, Feb 24 '14 at 05:27
@ICanHasCheezburger MySQL 5.0 and PHP .. I actually need the code for php.. — diego, Feb 24 '14 at 05:31

Ja͢ck · Answer 1 · 2014-02-24T06:35:31.670

3

If there are only four columns:

SELECT id, (d01 = 'B') + (d02 = 'B') + (d03 = 'B') + (d04 = 'B')
FROM tablename

No there are 31 columns

That's a problem which you can solve in two ways:

Repeat the condition for the other 27 columns :)
Normalize your structure so that each value is dependent on both the id and a numeric value that represents a calendar.

The PHP way

You can also fetch all columns and let PHP solve this for you:

$res = $db->query('SELECT * FROM tablename');
foreach ($res->fetchAll(PDO::FETCH_ASSOC) as $row) {
    $id = $row['id'];
    unset($row['id']); // don't count the id column
    $count = count(array_keys($row, 'B', true));

    printf("ID %d: %d\n", $id, $count);
}

edited Feb 24 '14 at 06:35

answered Feb 24 '14 at 03:48

Ja͢ck

170,779
38
263
309

No there are 31 columns :-D – diego Feb 24 '14 at 03:52
2

@diego Then simply add more conditions or normalize the table structure. – Ja͢ck Feb 24 '14 at 03:52
But how can I display the results only for a single ID? Thanks – diego Feb 24 '14 at 04:01
@diego What do you mean? You mean after normalization? A bit confused. – Ja͢ck Feb 24 '14 at 04:02
Yes, sorry.. my fault! I have a table for each user and I want to display the number only for him.. Like: This month the value "B" is repeating $number times.. – diego Feb 24 '14 at 04:05
In that case you would have a join condition and then `SELECT id, COUNT(val = 'B') ... GROUP BY id` as the query. – Ja͢ck Feb 24 '14 at 04:09
I'm not familiar with the join function.. could you please post the complete code? Thanks a lot – diego Feb 24 '14 at 04:15
This is the code but is not working: $query = "SELECT userid, (d01 = 'AG') + (d02 = 'AG') + (d03 = 'AG') + (d04 = 'AG') FROM january"; $resultquery = mysql_query($query) or die (mysql_error()); $q = mysql_fetch_assoc($resultquery); $countquery=mysql_num_rows($resultquery); – diego Feb 24 '14 at 05:28
@diego I've updated my answer to include a php solution as well. – Ja͢ck Feb 24 '14 at 06:36

score 1 · Accepted Answer · edited May 23 '17 at 10:31

Since you seem to be using mysql_*:

// SHOW COLUMNS returns all the columns and constrains of the defined table
// We only need the column names so we will be later calling it by 'Field'
$sql = mysql_query("SHOW COLUMNS FROM table"); //your table name here
$val_to_count = 'B'; //value to count here
$id = 1; //id to search for
$new_sql = 'SELECT id, ';

// In this loop we will construct our SELECT query using the columns returned 
// from the above query
while($row=mysql_fetch_array($sql)){
    if($row['Field']!='id'){
            $new_sql .= ' ('.$row['Field'].' = "'.$val_to_count.'") + ';
    }
}

//Removing the last "+ " produced in the select query
$new_sql = rtrim($new_sql,"+ ");
$new_sql .= ' as count FROM table WHERE id = '.$id; //table name here again
// so $new_sql now has an output like: 
// SELECT ID, (d01 = 'B') + (d02 = 'B') ... WHERE id = 1

$sql2 = mysql_query($new_sql);
//executing the constructed query with the output below
while($row2=mysql_fetch_array($sql2)){
 echo 'ID - '.$row2['id']."<br>";
 echo 'Count - '.$row2['count']."<br>";
}

Note:

mysql_* is deprecated, please consider to migrate to mysqli_*

Working perfectly.. Now I have to understand how :) .. Unfortunately the server is not mine and I can't decide the version. — diego, Feb 24 '14 at 05:50
@diego I have updated the answer with in-line code explanation, you might want to read them to understand what it does. Feel free to ask for more. PS: Skip all the above comments, they are not related to the question/answer. — AyB, Feb 24 '14 at 10:21

Count same values in different column mysql

2 Answers2

The PHP way

Note: