SELECT billing_state, AuctionZip, Craigslist, Flyer, Friends, Newspaper, Radio, Search_Engine, Social_Media, TV, Other, Total FROM

(SELECT

IF(us.state IN ('WA','OR','CA','ID','NV','AZ','UT','MT','WY','CO','NM','TX','OK','KS','NE','SD','ND','arizona','Arizona','Texas'),'AZ',IF(us.state IN ('MN','IA','MO','IL','WI','MI','IN','KY','OH','Indiana','Indianapolis','Kentucky'),'IN',IF(us.state IN ('AR','TN','LA','MS','AL'),'TN',IF(us.state IN ('WV','VA','NC','SC','GA','FL','Georgia','South Carolina'),'SC',IF(us.state IN ('ME','NH','VT','MA','RI','CT','NJ','PA','NY','DE','MD','DC','PA Pennsylvania','Pennsylvania'),'PA',IF(us.state='',NULL,us.state)))))) billing_state,

SUM(IF(referrer.text LIKE "%auctionzip%",1,0)) AuctionZip,

SUM(IF(referrer.text LIKE "%craigslist%",1,0)) Craigslist,
SUM(IF(referrer.text LIKE "%flyer%",1,0)) Flyer,

IF(referrer.text LIKE "%flyer%",1,0)

SUM(IF(referrer.text LIKE "%friends%",1,0)) Friends,

SUM(IF(referrer.text LIKE "%newspaper%",1,0)) Newspaper,

SUM(IF(referrer.text LIKE "%radio%",1,0)) Radio,

SUM(IF(referrer.text LIKE "%search%",1,0)) Search_Engine,

SUM(IF(referrer.text LIKE "%social%",1,0)) Social_Media,

SUM(IF(referrer.text LIKE "%tv%",1,0)) TV,

SUM(IF(referrer.text LIKE "%other%",1,0)) Other,

SUM(IF(referrer.text IS NOT NULL AND referrer.text <> '',1,0)) Total FROM user u

INNER JOIN user_info ui ON (ui.user_id=u.id)

INNER JOIN user_billing us ON (us.user_id=u.id)

INNER JOIN user_cust_data referrer ON (referrer.user_id=u.id AND referrer.user_cust_field_id=2)

INNER JOIN user_cust_data referrer ON (referrer.user_id=u.id AND referrer.user_cust_field_id=3)

WHERE u.created_on {DateRange} AND referrer.text IS NOT NULL AND referrer.text <> ''

GROUP BY billing_state) Temp

ORDER BY Total DESC;