Generate a report for multiple locations

37
September 22, 2018, at 8:20 PM

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.

Answer 1

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

Answer 2

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'
Answer 3

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();
READ ALSO
Requesting help in reading blob datatype in mariadb via python

Requesting help in reading blob datatype in mariadb via python

I am trying to print the content inside blob data type and here is my python code and as well the error

67
Unexpected MYSQL UNION and extractvalue behaviour

Unexpected MYSQL UNION and extractvalue behaviour

I was attempting an error-based sql injection when I discovered a weird behavior that I can't explainHere is a simple example:

41
Determine/create a geofence/bounding box on the fly

Determine/create a geofence/bounding box on the fly

I have a MySQL table of lat/lon (think of a school campus or shopping mall)

37
Count Case Statement - When One Field Greater Than Another

Count Case Statement - When One Field Greater Than Another

I'm trying to determine how pervasive a particular mistake is in my databaseI'm comparing one field against another, and when that field is greater then the other, I want it to count it

50