php - Display highest value for count using case

Question

I have voting system that consist of table position, profil, position_applied and vote

1. Position

| id_position    | name          |active|
| --------       | ------------- |------|
|     1          | president     |  Y   |
|     2          | secretary     |  Y   |
|     3          | vice president|  Y   |

2. profil (the candidate)

| id_profil      | name          |approve|active|
| --------       | ------------- |------ |------|
|     1          | James         |  Y    |  Y   |
|     2          | Anne          |  Y    |  Y   |
|     3          | Michael       |  Y    |  Y   |
|     4          | Kenny         |  Y    |  Y   |
|     5          | Rachel        |  Y    |  Y   |
|     6          | Zayn          |  Y    |  Y   |
|     7          | Lily          |  Y    |  Y   |
|     8          | Emily         |  Y    |  Y   |

3. position_applied (the candidate id with position id)

| id   | id_profil |id_position|active|
| -----|-----------|-----------|------|
|   1  |     1     |  1        |  Y   |
|   2  |     2     |  2        |  Y   |
|   3  |     3     |  2        |  Y   |
|   4  |     4     |  3        |  Y   |
|   5  |     5     |  3        |  Y   |
|   6  |     6     |  3        |  Y   |
|   7  |     7     |  1        |  Y   |
|   8  |     8     |  2        |  Y   |

3. vote (note that the data in column president, secretary and vice president is the id_profil of candidate)

| id   | voter_id  |president  |secretary |vice president|
| -----|-----------|-----------|------    |--------------|
|   1  |     121   |  1        |  2       |      4       |
|   2  |     278   |  1        |  2       |      5       |
|   3  |     398   |  1        |  3       |      5       |
|   4  |     474   |  7        |  8       |      5       |
|   5  |     598   |  7        |  8       |      5       |
|   6  |     677   |  7        |  8       |      5       |
|   7  |     777   |  7        |  8       |      6       |
|   8  |     218   |  7        |  8       |      5       |

I'm trying to display the highest vote for each position so that it will display:

| id   |id_position |id_profile|number_of_vote|
| -----|----------- |----------|--------------|
|   1  |     1      |  7       |  5           |
|   2  |     2      |  8       |  5           | 
|   3  |     3      |  5       |  6           |

I have tried various way to display it but still not succeed. Below is the summary of my code just to show the flow of how I fetch the data:

SELECT * FROM position WHERE active = 'Y'

$id = id_position

WHILE

SELECT * FROM profil INNER JOIN position_applied ON position_applied.id_profil = profil.id_profil
WHERE profil.approve = 'Y' AND profil.active = 'Y' AND position_applied.id = '$id'

WHILE

$id_candidate = id_profil

SELECT COUNT(CASE WHEN president = '$id_candidate' OR secretary = '$id_candidate' OR vice president = '$id_candidate' THEN 1 END) AS total FROM vote WHERE ORDER BY COUNT(CASE WHEN president = '$id_candidate' OR secretary = '$id_candidate' OR vice president = '$id_candidate' THEN 1 END) DESC

question from:https://stackoverflow.com/questions/65895571/display-highest-value-for-count-using-case

Answer 1

Waitting for answers