如何在一列中使用 SUM 从数据库中检索数据的值

Question

我有一个函数，可以从数据库中检索值作为json。我需要来自数据库的一些数据和某些列的 SUM 值。当我没有从这些值中检索 SUM 时，我会得到所有必需的数据，但是当我还包括 SUM 函数从某些列中获取值的总和时，我只得到一个值对象

型

public function search_tickets($data){
$ticketDate = $data['ticket_date'];
$ticketBus = $data['ticket_bus'];
$ticketAgentId = $data['agent_id'];
$user = $this->db->select("
CONCAT_WS(' ', tp.firstname, tp.lastname) AS passenger_name,
tb.seat_numbers AS seat_number,
tb.id_no AS ticket_number,
fr.reg_no AS bus_number,
DATE_FORMAT(ta.start_date, '%d/%m/%Y') as ticket_date,
tb.price AS fare_paid,
tb.pickup_trip_location AS boarding,
tb.drop_trip_location AS dropping,
SUM(tb.price) AS total_fare
")
->from('tkt_booking AS tb')
->join('tkt_passenger AS tp', 'tb.tkt_passenger_id_no = tp.id_no' ,'full')
->join('trip_assign AS ta', 'ta.id = tb.trip_id_no' ,'full')
->join('trip_route AS tr', 'tr.id = tb.trip_route_id','full')
->join('agent AS a', 'a.agent_id = tb.agent_id','full')
->join('fleet_registration AS fr', 'fr.id = ta.fleet_registration_id','full')
->where('ta.start_date', $ticketDate )
->where('fr.reg_no', $ticketBus )
->where('tb.agent_id', $ticketAgentId )
->get()
->result();
return $user;
}

控制器

public function searchTickets(){ 
$data = $_POST; 
$ticket=$this->booking_model->search_tickets($data);
if($ticket){
$result =  array('tickets'=>$ticket);
} else {
$result =  NULL;
}
print json_encode($result);
} 

Answer 1

您应该GROUP BY乘客 ID 以获得每位乘客的总金额：

$this->db->group_by('tb.tkt_passenger_id_no');