ALTER TABLE `city_state_listings_counts` ADD `city` VARCHAR(100) NULL DEFAULT NULL AFTER `total`, ADD `state` VARCHAR(100) NULL DEFAULT NULL AFTER `city`, ADD `state_code` VARCHAR(10) NULL DEFAULT NULL AFTER `state`;

TRUNCATE city_state_listings_counts;

insert into city_state_listings_counts (select concat(cq.city,', ',cq.state) as city_state, cq.total, cq.city, sl.stateName, cq.state from (SELECT `city`,`state`, count(id) as total FROM `listings` where city <> '' and city is not null and state <> '' and state is not null group by city, state) as cq join states_list sl on sl.shortCode = cq.state); 