Count different occurrences of elements from multiple columns in a pattern
I am working on a polling application using MySql and PHP Answers will be in the following format:
+-c1_1-+-c1_2-+-c1_3-+-c1_1-+-c1_2-+-c1_3-+-....
+ red + blue + pink + cyan + red + gray + ....
+ black+ pink + plum + red + blue + gray + ....
+ cyan + red + blue + blue + pink + plum + ....
+------+------+------+------+------+------+ ....
- c1_1 represents Column_For_Question_1_With_Rank_1
- c1_2 represents Column_For_Question_1_With_Rank_2
- c1_3 represents Column_For_Question_1_With_Rank_3
- c2_1 represents Column_For_Question_2_With_Rank_1
- c2_2 represents Column_For_Question_2_With_Rank_2
- c2_3 represents Column_For_Question_2_With_Rank_3
The count looks like this:
- Rank 1 = color in column cX_1 = gets 3 labels (c1_1, c2_1, c3_1 ..)
- Rank 2 = color in column cX_2 = gets 2 labels (c1_2, c2_2, c3_2 ..)
- Rank 3 = color in column cX_3 = gets 1 label (c1_3, c2_3, c3_3 ..)
Red rating:
- appears in cX_1 twice = 3x2 = 6
- appears in cX_2 twice = 2x2 = 4
- So Red gets a score of 6 + 4 = 10
Blue rating:
- appears in cX_1 once = 3x1 = 3
- appears in cX_2 twice = 2x2 = 4
- appears in cX_3 twice = 1x2 = 2
- So blue gets a score of 3 + 4 + 2 = 9
Is it possible to write an efficient query to get the result like:
+-color-+-score-+
+ red + 10 +
+ blue + 9 +
+ xxx + # +
+ xxx + # +
+ xxx + # +
+-------+-------+
If this is not possible, at least the number of events such as:
+-color-+-n_cX_1-+-n_cX_2-+-n_cX_3-+
+ red + 2 + 2 + 0 +
+ blue + 1 + 2 + 2 +
+ xxx + # + # + # +
+ xxx + # + # + # +
+ xxx + # + # + # +
+ xxx + # + # + # +
+-------+--------+--------+--------+
In fact, the colors will be replaced by the names of the people.
Each "set of three consecutive columns" (cX_1, cX_2, cX_3) represents the first, second and third ranks scored on each of the 9 questions. So there will be 3x9 = 27 columns
Can someone please help me with this? I think I can use count (*) more than once, but I'm pretty sure this is the wrong approach. Searched a lot before posting but couldn't solve it.
Edit 1: Want to mention that there might be around 50 people in these columns. And each line will represent the answer from one examiner doing the survey. There will be about 100 such examiners and therefore about 100 lines.
source to share
Here is my solution, I'm at war with PHP more, so my answer will be on the PHP side, if you want a SQL solution, you have to wait a while to allow another user to add the SQL solution.
You will get an array like this in PHP from MySQL,
$result = [
'c1_1'=>['red','black','cyan'],
'c1_2'=>['blue','pink','red'],
'c1_3'=>['pink','plum','blue'],
'c2_1'=>['cyan','red','blue'],
'c2_2'=>['red','blue','pink'],
'c2_3'=>['gray','gray','plum']
];
Now create an array of users having every custom achievement in every category,
$users = [];
foreach($result as $k => $v)
{
foreach($v as $user)
{
$users[$user][] = $k;
}
}
Now the $ users array will look like,
array (size=7)
'red' =>
array (size=4)
0 => string 'c1_1' (length=4)
1 => string 'c1_2' (length=4)
2 => string 'c2_1' (length=4)
3 => string 'c2_2' (length=4)
'black' =>
array (size=1)
0 => string 'c1_1' (length=4)
'cyan' =>
array (size=2)
0 => string 'c1_1' (length=4)
1 => string 'c2_1' (length=4)
'blue' =>
array (size=4)
0 => string 'c1_2' (length=4)
1 => string 'c1_3' (length=4)
2 => string 'c2_1' (length=4)
3 => string 'c2_2' (length=4)
'pink' =>
array (size=3)
0 => string 'c1_2' (length=4)
1 => string 'c1_3' (length=4)
2 => string 'c2_2' (length=4)
'plum' =>
array (size=2)
0 => string 'c1_3' (length=4)
1 => string 'c2_3' (length=4)
'gray' =>
array (size=2)
0 => string 'c2_3' (length=4)
1 => string 'c2_3' (length=4)
Now define a function to calculate the labels by passing the array to the user we just created,
function marks_of($input)
{
$marks_index = ['_1'=>3, '_2'=>2, '_3'=>1]; // define marks here
$marks = 0;
foreach($input as $marking)
{
$marks += $marks_index[substr($marking, -2)];
}
return $marks;
}
You need to define the labels in each category, as I commented on the above code.
Now use it like
$marks_of_red = marks_of($users['red']);
will give
int 10
To create an array, each tagged by name,
$all_users_marks = [];
foreach($users as $name => $achievements)
{
$all_users_marks[$name] = marks_of($users[$name]);
}
Now $all_users_marks
there is
array (size=7)
'red' => int 10
'black' => int 3
'cyan' => int 6
'blue' => int 8
'pink' => int 5
'plum' => int 2
'gray' => int 2
As I said, wait for someone if you want a two-way MySQL answer.
source to share