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
– diego, Commented Feb 24, 2014 at 3:49
How about getting the whole information into a PHP array and using PHP array functions for this? — AyB
– AyB, Commented Feb 24, 2014 at 5:05

Ja͢ck · Accepted Answer · 2014-02-24 06:35:31Z

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, 2014 at 6:35

answered Feb 24, 2014 at 3:48

Ja͢ck

174k39 gold badges269 silver badges316 bronze badges

Sign up to request clarification or add additional context in comments.

9 Comments

diego Over a year ago

No there are 31 columns :-D

Ja͢ck Over a year ago

@diego Then simply add more conditions or normalize the table structure.

diego Over a year ago

But how can I display the results only for a single ID? Thanks

Ja͢ck Over a year ago

@diego What do you mean? You mean after normalization? A bit confused.

diego Over a year ago

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..

|

Community · Accepted Answer · 2017-05-23 10:31:57Z

1

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_*

edited May 23, 2017 at 10:31

CommunityBot

11 silver badge

answered Feb 24, 2014 at 5:40

AyB

11.6k4 gold badges34 silver badges49 bronze badges

2 Comments

diego Over a year ago

Working perfectly.. Now I have to understand how :) .. Unfortunately the server is not mine and I can't decide the version.

AyB Over a year ago

@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.

Collectives™ on Stack Overflow

Count same values in different column mysql

2 Answers 2

The PHP way

9 Comments

Note:

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

The PHP way

9 Comments

Note:

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related