为多个位置生成报告

I am attempting to achieve the following result in CodeIgniter

SELECT location, COUNT(location), AVG(review) FROM progrodb.tickets WHERE datesubmitted BETWEEN '2018-9-1' AND '2018-9-30' AND location = 'location'

The output needs to be;

Location|Total Tickets|Avg Review<br>
location|3            |4.5

This table should include the result for each location. The SQL statement as is provide the results for a single location now I need this to be done for a total of 22 locations.

I have tried the following attempt but after a var_dump() results return null

public function generatereport(){
        // Set Page Title
        $this->data['page_title'] = 'Generate Report';

        $rules = $this->support_m->rules_report;
        $this->form_validation->set_rules($rules);

        $startdate = $this->input->post('startdate');
        $enddate = $this->input->post('enddate');
        define('locations', array('Shoppers Fair Blue Diamond', 'Shoppers Fair Burke Road', 'Shoppers Fair Brunswick', 'Shoppers Fair Duhaney Park', 'Shoppers Fair Greater Portmore', 'Shoppers Fair View', 'Shoppers Fair Junction', 'Shoppers Fair Liguanea', 'Shoppers Fair Manchester'));

         if ($this->form_validation->run() == TRUE){
            $results = $this->db->select('location, count(location) as location_count, AVG(review) as review_avg')
            ->where('datesubmitted BETWEEN "'.$startdate.'" AND "'.$enddate.'"')
            ->group_by('location') 
            ->get('tickets')->result();
            var_dump($results);

         }

        // Load view    
        $this->data['subview'] = 'admin/tickets/report';
        $this->load->view( 'admin/body', $this->data );

    }

And got the following dump now I'm trying to pass the results to the view but I receive the errors Undefined variable: reports and Trying to get property of non-object.

The code below worked as intended:

In the Controller

public function generatereport(){
        // Set Page Title
        $this->data['page_title'] = 'Generate Report';
        $this->data['reports'] = null;

        $rules = $this->support_m->rules_report;
        $this->form_validation->set_rules($rules);

        $startdate = $this->input->post('startdate');
        $enddate = $this->input->post('enddate');

        if ($this->form_validation->run() == TRUE){
            $results = $this->db->select('location, count(location) as locationcount, AVG(review) as reviewavg')
            ->where('datesubmitted BETWEEN "'.$startdate.'" AND "'.$enddate.'"')
            ->group_by('location') 
            ->get('tickets')->result();
            $this->data['reports'] = $results;
        }

        // Load view    
        $this->data['subview'] = 'admin/tickets/report';
        $this->load->view( 'admin/body', $this->data );

    }

In the View

<table class="table table-striped">
                    <thead>
                        <tr>
                            <th>Location</th>
                            <th>Total Tickets</th>
                            <th>Avg Review</th>
                        </tr>
                    </thead>
                    <tbody>
                        <?php 
                            if(count($reports)): foreach($reports as $ticketreport): 
                        ?>
                        <tr>
                            <td><?php echo $ticketreport->location; ?></td>
                            <td><?php echo $ticketreport->locationcount; ?></td>
                            <td><?php echo $ticketreport->reviewavg; ?></td>
                        </tr>
                        <?php endforeach; ?>
                        <?php else: ?>
                            <tr>
                                <td colspan="4">We could not find any records.</td>
                            </tr>
                            <?php endif; ?> 
                    </tbody>
                </table>

Thanks to everyone who contributed.

I think you need smth like this

SELECT location, COUNT(location), AVG(review) 
FROM progrodb.tickets
WHERE datesubmitted BETWEEN '2018-9-1' AND '2018-9-30'
GROUP BY location = 'location'

You can use the following to generate a report for multiple locations:

   $results = $this->db->select('count(location), AVG(review)')
   ->where('datesubmitted BETWEEN "2018-9-1" AND "2018-9-30"')
   ->group_by('location') 
   ->get('tickets')->result();

If I understood what you want, something like this can help:

SELECT location, COUNT(location) as location_count, AVG(review) as review_avg
FROM progrodb.tickets
WHERE datesubmitted BETWEEN '2018-09-01' AND '2018-09-30'
GROUP BY location

I have aliased the count and the average for readability

I think you need the HAVING clause for aggregate functions. Something like this might work:

SELECT
    location,
    COUNT( location ),
    AVG( review ) 
FROM
    progrodb.tickets 
WHERE
    datesubmitted BETWEEN '2018-9-1' 
    AND '2018-9-30' 
HAVING
    location = 'location'