How to count max occurence of rating

Question

Sophia_1 0 Junior Poster in Training

11 Years Ago

Hi everyone...i have this table ie Kra,Year,Max(Rating1),Max(Rating2),Tot_Rating...the task is to display the max occurence of rating.There are 2 types of rating ie "good" or "problematic"..if Max(Rating1)="good" and Max(Rating2)="good", Tot_Rating="good".Below is the coding but it didn't display as what suppose to...please advise...thanks a lot.

<!DOCTYPE html>
<html>
<body>

<?php
$connection = mysql_connect("localhost","pqa","") or die("Database connection failed!<br>");
$result=mysql_select_db("pqad") or die("Database could not be selected!");
echo(max (array("Max(Rating1)","Max(Rating2)" . "<br>")));

?>
</body>
</html>

mysql php

Edited 11 Years Ago by Member #120589 because: you forgot to use code tags

4 Contributors
14 Replies
352 Views
1 Week Discussion Span
Latest Post 11 Years Ago Latest Post by Member #120589

Dani AI

Generated 9 Months Ago

A short diagnosis and plan based on the thread: the SQL function MAX() finds the largest value, not the most frequent value (mode) — as already pointed out. The original PHP attempted array/max logic and then tried to COUNT(MAX(...)) which is invalid. The clean approach is: (1) find the mode per KRA for Quarter 1 and Quarter 2, (2) decide TotRating from those two modes (either require both equal, or pick the rating with the higher combined count), and (3) render the status cell with a color. ’s suggestion to move off deprecated mysql* calls is still important.

MySQL 8+ (recommended) — get per-quarter modes, total counts, then pick Tot_Rating (majority / combined-count tie-breaker):

-- assumes: kra(kraid,kra), progress(progressid,kraid,quarter,rating)
WITH quarter_counts AS (
  SELECT kraid, quarter, rating, COUNT(*) AS cnt
  FROM progress
  GROUP BY kraid, quarter, rating
),
quarter_modes AS (
  SELECT kraid, quarter, rating AS mode, cnt,
         ROW_NUMBER() OVER (PARTITION BY kraid, quarter ORDER BY cnt DESC, rating) rn
  FROM quarter_counts
),
combined AS (
  SELECT kraid, rating, SUM(cnt) AS total_cnt
  FROM quarter_counts
  GROUP BY kraid, rating
)
SELECT k.kraid, k.kra,
       q1.mode AS q1_mode, q1.cnt AS q1_cnt,
       q2.mode AS q2_mode, q2.cnt AS q2_cnt,
       CASE
         WHEN q1.mode = q2.mode THEN q1.mode
         WHEN COALESCE(c1.total_cnt,0) >= COALESCE(c2.total_cnt,0) THEN q1.mode
         ELSE q2.mode
       END AS tot_rating
FROM kra k
LEFT JOIN quarter_modes q1 ON q1.kraid = k.kraid AND q1.quarter = 1 AND q1.rn = 1
LEFT JOIN quarter_modes q2 ON q2.kraid = k.kraid AND q2.quarter = 2 AND q2.rn = 1
LEFT JOIN combined c1 ON c1.kraid = k.kraid AND c1.rating = q1.mode
LEFT JOIN combined c2 ON c2.kraid = k.kraid AND c2.rating = q2.mode;

Fallback for older MySQL: use two correlated subqueries to get each quarter mode and a small aggregate subquery for the combined-mode; compute TotRating in the application if that is simpler. Example rendering for status color in PHP (use mysqli or PDO, not deprecated mysql* functions):

$color = (strtolower($row['tot_rating']) === 'good') ? '#008000' : '#FF0000';
echo "<td style=\"background-color:{$color};\">{$row['tot_rating']}</td>";

Troubleshooting notes: normalize the schema (one rating per row with a quarter column) if possible; make rating values consistent (ENUM or tinyint with mapping) to avoid case/whitespace mismatches; add indexes on (kraid, quarter, rating) for performance; decide and document a tie-breaker policy (e.g., require both quarters to match for Tot_Rating, or prefer the rating with the larger combined count).

All 14 Replies

pzuurveen 90 Posting Whiz in Training

11 Years Ago

You don't do a query
checkout www.tizag.com,
www.w3schools.com

Edited 11 Years Ago by pzuurveen

Member #120589

11 Years Ago

Aren't you looking for the "max of count". Max() will give the highest value in a column (field) - not the "mode" value. A simple mode on a single field:

SELECT COUNT(field) AS mode FROM table GROUP BY field ORDER BY mode DESC LIMIT 1;

Reference: http://lists.mysql.com/mysql/155540

Edited 11 Years Ago by Member #120589

Member #120589

11 Years Ago

Please repost with correct code formatting. Place your code into the code editor (after pressing the </> Code button).

Member #120589

11 Years Ago

WHat are you trying to colour? You're just printing out the hex value of the last row's 'mode' to the screen below the table.

$class = ($row['mode'] == 'good') ? 'green' : 'red';
echo "<td class='$class'>" . $row['mode'] . "</td>";

For this you need:

.green{
    color: #008000; 
}

.red{
    color: #FF0000; 
}

In your css or style tags

Sophia_1 commented: Hi...am trying to color the Status column/field...pls advise...thanks +0

Reply to this topic

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.

Hi...am trying to color the Status column/field...pls advise...thanks

raicabogdan 0 Newbie Poster · Answer 1 · 2014-10-03T15:09:59+00:00

Indeed, you forgot to make your mysql_query there. The mysql query will get you the exact value you need.

also keep in mind, we all here recommend you at least switch to mysqli_* functions!

Sophia_1 0 Junior Poster in Training · Answer 2 · 2014-10-06T04:14:43+00:00

Hi everyone...thanks a lot for your help...really appreciate it...now i have to display different color based on the Tot_Rating,so if the Tot_Rating="Good" the Status field is "Green" and if Tot_Rating="Problematic", the Status field is "Red".Below is the coding but it doesn't display as supposed to.Please advise...Thanks a lot

<?php
$con=mysqli_connect("localhost","pqa","","pq");
// Check connection

if (mysqli_connect_errno()) {

echo "Failed to connect to MySQL: " . mysqli_connect_error();

$result = mysqli_query($con,"SELECT * FROM total_rating");
echo "<table border='1'>
<tr>
<th>KRA(Key Result Areas)</th>
<th>Rating</th>
<th>Status</th>

</tr>";

while($row = mysqli_fetch_array($result)) {

echo "<tr>";
echo "<td>" . $row['Kra'] . "</td>";
echo "<td>" . $row['mode'] . "</td>";
echo "</tr>";

echo "</table>";

switch ($row['mode']) { 



case "good"; 
    echo "#008000"; 
    break; 
default;     
    echo "#FF0000"; 
    break;     


}

mysqli_close($con);
?>

Sophia_1 0 Junior Poster in Training · Answer 3 · 2014-10-07T02:00:41+00:00

Hi everyone...thanks a lot for your help...really appreciate it...now i have to display different color based on the Tot_Rating,so if the Tot_Rating="Good" the Status field is "Green" and if Tot_Rating="Problematic", the Status field is "Red".Below is the coding but it doesn't display as supposed to. Please advise...Thanks a lot

<?php
$con=mysqli_connect("localhost","pqa","","pq");
// Check connection

if (mysqli_connect_errno()) {
echo "Failed to connect to MySQL: " . mysqli_connect_error();

}
$result = mysqli_query($con,"SELECT * FROM total_rating");
echo "<table border='1'>
<tr>
<th>KRA(Key Result Areas)</th>
<th>Rating</th>
<th>Status</th>

</tr>";

while($row = mysqli_fetch_array($result)) {
echo "<tr>";
echo "<td>" . $row['Kra'] . "</td>";
echo "<td>" . $row['mode'] . "</td>";
echo "</tr>";

}
echo "</table>";

switch ($row['mode']) { 
case "good"; 
    echo "#008000"; 
    break; 
default;     
    echo "#FF0000"; 
    break;     
} 
mysqli_close($con);
?>

Sophia_1 0 Junior Poster in Training · Answer 4 · 2014-10-08T07:07:33+00:00

Hi everyone...sorry but i still can't get the Tot_Rating for the above..Below is the mysql select statement but there is error...The mode seems to take the Max(Rating2) value, not the most occurred value...Please advise...Thanks you.

SELECT COUNT(Max(Rating1),Max(Rating2)) as mode FROM progress GROUP BY (Max(Rating1),Max(Rating2)) ORDER BY mode DESC LIMIT 1;

Sophia_1 0 Junior Poster in Training · Answer 5 · 2014-10-11T05:09:55+00:00

Hi...am trying to color the Status column/field...pls advise...thanks...

Member #120589 · Answer 6 · 2014-10-11T11:14:57+00:00

WHy are you doing this:

COUNT(Max(Rating1),Max(Rating2))

ANd:

GROUP BY (Max(Rating1),Max(Rating2))

That doesn't make any sense to me.
Why have you got two ratings fields?
What type of data is stored in both?
Are they the same?

Please give an example of output of 5 records to give us an idea of what we're looking at.

Sophia_1 0 Junior Poster in Training · Answer 7 · 2014-10-13T00:59:48+00:00

Hi...am trying to output the mode or Tot_Rating for the Max(Rating1),Max(Rating2), where Max(Rating1) is the maximum occurence from many Kra(Key result area) for Quarter 1 and Max(Rating2) is the maximum occurence from many Kra(Key result area) for Quarter 2. These Kra has many progress. Pls advise....Thank you...

Output example:

Kra Tot_Rating Status
Tutoring Good Green color column
Research Problematic Red color column
Staff Good Green color column
Cost Problematic Red color column
Admin Problematic Red color column

Member #120589 · Answer 8 · 2014-10-13T08:47:41+00:00

I meant the records in the original database tables - I am not convinced that you've got this right. Show any linked db tables too.

Sophia_1 0 Junior Poster in Training · Answer 9 · 2014-10-13T09:39:58+00:00

The Kra has many progress. Below is example of the table records:

a.Kra table
Kraid Kra
1. Tutoring
2. Research
3. Staff
4. Cost
5. Admin

b.Progress table

Progressid Quarter1 Rating1      Quarter2 Rating2      Kraid

1 1 Good 2 Good 1
2 1 Problematic 2 Problematic 1
3 1 Good 2 Good 1

The Max(Rating1) is the max occurence for Rating1 and Max(Rating2) is the max occurence for Rating2. In addition would also want to find the Tot_Rating ie mode for Max(Rating1) and Max(Rating2).

Appreciate for your advise. Thanks a lot.

Member #120589 · Answer 10 · 2014-10-13T09:57:56+00:00

Progress table could be:

progress_id [pk]
kraid [fk]
quarter [accept tinyint 1-4]
rating [tinyint 1-2 1= good,2=prob]

How to count max occurence of rating

Dani AI

Recommended Answers Collapse Answers

All 14 Replies

Recommended Answers