Grouping and Counting Multiple Values in a Column

Question

I'm trying to generate a report for how many jobs we do in each city each day, also showing where we get that job from.

I have a column with a number of suburbs (roughly 300-350). These are suburbs in two cities. I'd like to group these suburbs into their appropriate city and also show the count.

I was thinking something like:

Site_Suburb LIKE '%Suburb1%' OR LIKE '%Suburb2%' AS `City1` 
Site_Suburb LIKE '%Suburb3%' OR LIKE '%Suburb4%' AS `City2 

(Just rough pseudo code to help give you an idea..)

Overall I need the report to GROUP BY Date, City and Job Source

I have this query so far:

SELECT
from_unixtime(`reservations`.`created`) AS `formatted_date`,
`reservations`.`start_date` AS `start_date`,
`reservations`.`site_suburb` AS `site_suburb`,
`reservations`.`job_source` AS `job_source`,

DATE(FROM_UNIXTIME(`reservations`.`created`)) AS date,

COUNT(`reservations`.`created`) as group_by_count from `reservations`

GROUP BY
DATE(FROM_UNIXTIME(`reservations`.`created`)),
`reservations`.`site_suburb`,
`reservations`.`job_source`

order by from_unixtime(`reservations`.`created`) desc

Thanks in advance ^_^

Answer 1

select
    case
         when Site_Suburb LIKE '%Suburb1%' OR Site_Suburb LIKE '%Suburb2%' AS `City1` 
         when Site_Suburb LIKE '%Suburb3%' OR Site_Suburb LIKE '%Suburb4%' AS `City2 

    else site_suburb end Suburb
    ,job_source
    ,DATE(FROM_UNIXTIME(`reservations`.`created`)) AS date
    ,count(*) as jobs

from 
    reservations

group by
   case
         when Site_Suburb LIKE '%Suburb1%' OR LIKE '%Suburb2%' AS `City1` 
         when Site_Suburb LIKE '%Suburb3%' OR LIKE '%Suburb4%' AS `City2 

    else site_suburb end 
  ,job_source
  ,DATE(FROM_UNIXTIME(`reservations`.`created`))