sql - Mysql Pivot View -
i have mysql table below
username interval totalmails user1 10:00 3 user2 10:00 1 user3 10:00 1 user6 10:00 4 user7 10:00 4 user8 10:00 5 user9 10:00 2 user10 10:00 5 user2 11:00 4 user3 11:00 7 user5 11:00 4 user6 11:00 4 user8 11:00 4 user9 11:00 3 user4 9:00 9 user5 9:00 1 user7 9:00 7 user8 9:00 3 i looking pivot view output below:
username 9:00 10:00 11:00 grand total user1 3 3 user2 1 4 5 user3 1 7 8 user4 9 9 user5 1 4 5 user6 4 4 8 user7 7 4 11 user8 3 5 4 12 user9 2 3 5 grand total 20 25 26 71 can done 1 mysql query without using prepare , execute ?
probably select query
currently doing php. below mycode:
$data = array(); $d = array(); $heading = array('username'); $c = array('grand total' => 0); //$data['usernames']['username'] = 'username'; foreach ($res $key => $value) { $data['tbl'][$value['username']][strtotime($value['interval'])] = $value['mails']; $data['tbl']['grand total'][strtotime($value['interval'])] = ($data['tbl']['grand total'][strtotime($value['interval'])] + $value['mails']); //$data['tbl']['grand total']['grand total'] = ($data['tbl']['grand total']['grand total'] + $value['mails'])/2; $data['intervals'][strtotime($value['interval'])] = $value['interval']; $data['usernames'][$value['username']] = $value['username']; } foreach ($data['tbl'] $key => $value) { $data['tbl'][$key]['grand total'] = array_sum($value); } //$data['tbl'] = array_merge($data['tbl'], $d); //$data['tbl'] = array_merge($data['tbl'], $c); array_unique($data['intervals']); array_unique($data['usernames']); ksort($data['intervals']); $data['intervals']['grand total'] = 'grand total'; $data['usernames']['grand total'] = 'grand total'; $this->load->library('table'); $tmpl = array( 'table_open' => '<table class="table table-condensed table-striped table-bordered" id="sent_tbl">', 'heading_row_start' => '<tr>', 'heading_row_end' => '</tr>', 'heading_cell_start' => '<th style="text-align: center;">', 'heading_cell_end' => '</th>', 'row_start' => '<tr class="odd gradex">', 'row_end' => '</tr>', 'cell_start' => '<td style="text-align: center;">', 'cell_end' => '</td>', 'row_alt_start' => '<tr class="odd gradex">', 'row_alt_end' => '</tr>', 'cell_alt_start' => '<td style="text-align: center;">', 'cell_alt_end' => '</td>', 'table_close' => '</table>' ); $this->table->set_template($tmpl); $heading = array_merge($heading,array_values($data['intervals'])); $this->table->set_heading($heading); foreach ($data['usernames'] $uname => $unameval) { $row = array(); $row = array(array('data' => $unameval, 'style' => 'text-align:left;' ) ); if (strlen($unameval) > 1) { foreach ($data['intervals'] $key => $value) { if ($data['tbl'][$unameval][$key] > 0) { array_push($row, round($data['tbl'][$unameval][$key], 1)); } else { array_push($row, ''); } } } //echo '<pre>' . print_r($row, true) . '</pre>'; $this->table->add_row($row); } echo '<strong>hourly total:</strong>'; echo $this->table->generate();
try out this:
function get() { $this->db->select("username, avg(case when interval ='10:00' totalmail end) [10:00], avg(case when interval ='11:00' totalmail) end [11:00], avg(case when interval ='12:00' totalmail) end [12:00], sum(case when interval ='10:00' totalmail) end [grand_total]; $this->db->from('your_table_name'); $this->db->group_by('username'); } hope work you...!!!
do let me know: thanks
Comments
Post a Comment