0

I want to sum and display in table all ss_score_talent and ss_score_star which is equal to value of ss_contestant_name.

Model:

function get_average_score()
  {
    $this->db->select("event_contestant.cnt_id, event_contestant.cnt_fullname, event_score_sheet.ss_contestant_name, event_score_sheet.ss_date, event_score_sheet.ss_score_talent, event_score_sheet.ss_score_star, event_score_sheet_sc.sc_score_sc, event_score_sheet_pv.pv_score_pv");
    $this->db->from('event_contestant');
    $this->db->join('event_score_sheet', 'event_score_sheet.ss_contestant_name = event_contestant.cnt_fullname');
    $this->db->join('event_score_sheet_sc', 'event_score_sheet_sc.sc_contestant_name = event_contestant.cnt_fullname');
    $this->db->join('event_score_sheet_pv', 'event_score_sheet_pv.pv_contestant_name = event_contestant.cnt_fullname');
    $query = $this->db->get();
    return $query->result();
  }

Controller:

function read_averagescore()
  {
    $data = $this->Scoresheet_Model->get_average_score();
    echo json_encode($data);
  }

View: JS

function show_score_average()
  {
    $.ajax({
      type      : "GET",
      url       : "<?php echo base_url('scoresheet/read_averagescore');?>",
      async     : false,
      dataType  : "JSON",
      success   : function(data)
      {
        var html = '';
        var i;
        for(i=0; i<data.length; i++)
        {
          if (data[i].cnt_fullname == data[i].ss_contestant_name)
          {
            total_sc = math.sum(data[i].ss_score_talent, data[i].ss_score_talent, data[i].ss_score_talent);
          }
          var total_average = math.sum(data[i].ss_score_talent, data[i].ss_score_star, data[i].sc_score_sc, data[i].pv_score_pv);
          html +=
            '<tr>' +
              '<td>' + data[i].ss_date + '</td>' +
              '<td>' + data[i].cnt_fullname + '</td>' +
              '<td>' + math.sum(data[i].ss_score_talent,data[i].ss_score_talent,data[i].ss_score_talent) + '</td>' +
              '<td>' + data[i].ss_score_star + '</td>' +
              '<td>' + data[i].sc_score_sc + '</td>' +
              '<td>' + data[i].pv_score_pv + '</td>' +
              '<td>' + total_average + '</td>' +
            '</tr>';
        }
        $('#show_all_score_ca').html(html);
      }
    });
  }

see this output image

UPDATE: A friend of mine helped me to solve this. Here's the sql query.

"SELECT `ss_contestant_name`, SUM(`ss_score_talent`) / COUNT(`ss_contestant_name`) AS totalTalent, SUM(`ss_score_star`) / COUNT(`ss_contestant_name`) AS totalStar, (SELECT `sc_score_sc` FROM `event_score_sheet_sc` WHERE `sc_contestant_name` = `ss_contestant_name`) AS totalSC, (SELECT `pv_score_pv` FROM `event_score_sheet_pv` WHERE `pv_contestant_name` = `ss_contestant_name`) AS totalPV FROM `event_score_sheet` GROUP BY `ss_contestant_name`"
Dude Alexis
  • 1
  • 1
  • you can manage it by mysql query while select `SUM(IFNULL(coulmn1, 0) + IFNULL(coulmn2, 0) + IFNULL(coulmn3, 0)) AS totalsum` – PHP Ninja Nov 07 '19 at 11:05
  • Does this answer your question? [MySQL Sum() multiple columns](https://stackoverflow.com/questions/22369336/mysql-sum-multiple-columns) – PHP Ninja Nov 07 '19 at 11:06
  • can i give you a response using pure sql code instead codeigniter does to their querys? – Fernando Torres Nov 07 '19 at 20:37

0 Answers0