MySql - Count unique from colum

Question

Hi,

I have a mysql 'cities' table with data as follows:

id       name                               ascii_name

1       Abilene, Texas                      Abilene-Texas

2       Akron, Ohio                         Akron-Ohio

3       Albuquerque, New-Mexico             Albuquerque-New-Mexico

4       Alexandria, Virginia                Alexandria-Virginia

5       Allentown, Pennsylvania             Allentown-Pennsylvania

6       Amarillo, Texas                     Amarillo-Texas

etc...

I then have a 'Jobs' table that references the locations table.

I need to do a query that will return the number of jobs per state. How would I do this?

---

I'm not allowed to edit the database, only the function. The current function returns a list of cities and their job count, I need to edit this to return the states and their job count.

The current function looks like this:

public function GetJobsCountPerCity($excludeCitiesWithNoJobs)
{
 global $db;
 $jobsCountPerCity = array();

 $sql = 'SELECT city_id, COUNT(id) AS total FROM jobs WHERE is_temp = 0 AND is_active = 1 GROUP BY city_id'; 
 $result = $db->query($sql);

 while ($row = $result->fetch_assoc())
  $jobsCountPerCity[$row['city_id']] = $row['total'];

 $cities = get_cities();
 $result = array();
 foreach ($cities as $city)
 {
  $count = 0;

  // this check is needed because we don't have an entry if there are no jobs for a city
  if (isset($jobsCountPerCity[$city['id']]))
   $count = $jobsCountPerCity[$city['id']];

  if ($count > 0)
   $result[] = array('city_name' => $city['name'], 'jobs_in_city' => $count, 'city_ascii_name' => $city['ascii_name']);
  else 
  {
   if (!$excludeCitiesWithNoJobs)
    $result[] = array('city_name' => $city['name'], 'jobs_in_city' => $count, 'city_ascii_name' => $city['ascii_name']);
  }

 }
 return $result;
}

Thanks for your help!!

Answer 1

You need to have a state column in your locations table. Then join your tables together in your query, group by the state, and COUNT(*).

Break up your Location field into City, State, etc columns.

SELECT l.State, COUNT(*)
FROM jobs j
INNER JOIN locations l
   on j.location_id = l.ID
GROUP BY l.State
ORDER BY l.State

EDIT: If you can't change the table, and given your change in what the Locations table looks like, replace l.State in my query with

SUBSTRING(l.name, 
          CHARINDEX(',', l.name, 0) + 2, 
          LEN(l.name) - CHARINDEX(',', l.name, 0))

Answer 2

If you can you should be setting up your table(s) something like

COUNTRY:
country_key
country_desc
three_code
two_code

Records
1;united states;usa;us
2;canada;can;ca

STATE:
state_key
country_key
state_desc

Records:
1;1;alaska    (i.e usa, alaska)
2;1;alabama   
3;1;california

LOCATIONS:
id | country_key | state_key | city_name

Records:
1;1;3;los angeles   (i.e. usa, california, los angeles)


JOBS:
job_id
id    (FK into LOCATIONS)
....


Setting up your tables this way is better for a number of reasons, but in your case makes the query you need trivial.

select l.state_key, count(*) from jobs j join locations l on (j.id = l.id)
group by l.state_key;

Answer 3

This is an incomplete answer because your location field has quite a tricky structure:

SELECT substring(l.location, instr(l.location, '-')+1, 1000) state,
   count(*) jobscount
FROM jobs j INNER JOIN locations l
ON j.location=l.ID
GROUP BY substring(l.location, instr(l.location, '-'), 1000);

The reason it won't quite work is because on the one hand, you have some locations such as Anchorage-municipality-Alaska where the state does not come after the first hyphen; and on the other hand, some such as Albuquerque-New-Mexico where the state does not come after the last hyphen.

So I think you will probably need to create a third table of states and match them to the locations by joining a RIGHT() substring of the location name to the state name.

Or, as Andy suggested, create a new column in the location table and separate the state into it then group by that.

Answer 4

Leigh's answer almost works... use this

SELECT substring(cities.name, instr(cities.name, ', ')+1, 1000) state,
count(*) jobscount
FROM jobs INNER JOIN cities
ON jobs.city_id= cities.id
GROUP BY substring(cities.name, instr(cities.name, ', '), 1000)