CODEWITHSUNDEEP
phpmysql
Cule
Cule

Reputation: 117

How to make a sum for two queries in mysql

I need a concrete answer, thank you. I have this php code and query, as well as the table. What actually works here, in this query, values are added from the row, but it is not taken into account if they have a value of 0. It is necessary: to do this query, but also to get the values obtained and to divide the number of rows from the column depending on how they are entered:

EXAMPLE:

(4.20 + 5.00 + 4.80) / 3 number of rows = 4,67 TOTAL or

(4.20 + 5.00 + 4.80 + 3,10 + 5,00) / 5 number of rows = 4,42 TOTAL etc.

Sum of all rows divided by number of rows:

CREATE TABLE `ocene` 
(
    `id` int(11) NOT NULL,
    `predmeti` varchar(255) NOT NULL,
    `ocena1` varchar(255) NOT NULL,
    `ocena2` varchar(255) NOT NULL,
    `ocena3` varchar(255) NOT NULL,
    `ocena4` varchar(255) NOT NULL,
    `ocena5` varchar(255) NOT NULL,
    `ocena6` varchar(255) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

INSERT INTO `ocene` (`id`, `predmeti`, `ocena1`, `ocena2`, `ocena3`, `ocena4`, `ocena5`, `ocena6`) 
VALUES (1, 'French', '4', '5', '5', '5', '5', '0'),
       (2, 'Mathematics', '3', '5', '3', '2', '5', '5'),
       (3, 'English', '5', '5', '5', '0', '0', '0'),
       (4, 'Physics', '5', '3', '5', '4', '0', '0'),
       (5, 'Chemistry', '4', '4', '5', '4', '2', '0');

My php code:

    $sql = "SELECT predmeti, 
    ROUND((ocena1 + ocena2 + ocena3 + ocena4 + ocena5 + ocena6) /
          ((ocena1 != '0') + (ocena2 != '0') + (ocena3 != '0') + (ocena4 != '0') + (ocena5 != '0') + (ocena6 != '0')), 2) 
          AS final_count
    FROM ocene";

    if ($result=mysqli_query($con,$sql))
    {
    while ($row=mysqli_fetch_row($result))
      {
      printf ("%s = <b>%s</b> <br />",$row[0],$row[1]);
      }
    mysqli_free_result($result);
  }

The current result is:

French = 4,80
Mathematics = 3,83
English = 5,00
Physics = 4,25
Chemistry = 3,80

I need the following:

    French = 4,80
    Mathematics = 3,83
    English = 5,00
    Physics = 4,25
    Chemistry = 3,80
    ----------
    TOTAL: 4,34 <--- sum of all rows divided by number of rows

Upvotes: 2

Views: 223

Answers (2)

Arber Sokoli
Arber Sokoli

Reputation: 81

SELECT ROUND( sum( ROUND((ocena1 + ocena2 + ocena3 + ocena4 + ocena5 + ocena6) / ((ocena1 != '0') + (ocena2 != '0') + (ocena3 != '0') + (ocena4 != '0') + (ocena5 != '0') + (ocena6 != '0')), 2) ) / COUNT(id), 2) as Total FROM ocene

Upvotes: 0

LukStorms
LukStorms

Reputation: 29677

Try if a GROUP BY with an average and a WITH ROLLUP works for your version of MySql.

If each "predmeti" has only 1 record? Then it should be OK, since an AVG of 1 value equals the value.

SELECT COALESCE(predmeti, 'TOTAL') AS  predmeti, 
 ROUND(AVG((ocena1 + ocena2 + ocena3 + ocena4 + ocena5 + ocena6) / ((ocena1 != '0') + (ocena2 != '0') + (ocena3 != '0') + (ocena4 != '0') + (ocena5 != '0') + (ocena6 != '0'))),2) AS final_count
FROM ocene
GROUP BY predmeti WITH ROLLUP

Result:

predmeti    final_count
----------  --------------
Chemistry   3,8
English     5
French      4,8
Mathematics 3,83
Physics     4,25
TOTAL       4,34

Upvotes: 1

Related Questions