Monthly PV ex-US

gistfile1.txt

SELECT
  LEFT(timestamp,7) AS month, 
  SUM(CASE WHEN country NOT IN ("US", "Inv") THEN pageviews END) AS ex_us_total,
  SUM(CASE WHEN is_spider = 0 AND `is_automata` = 0 AND country NOT IN ("US", "Inv") THEN pageviews END) AS ex_us_human,
  SUM(CASE WHEN is_spider = 0 AND `is_automata` = 0 AND country NOT IN ("US", "Inv") AND `access_method` = "Desktop" THEN pageviews END) AS ex_us_human_desktop,
  SUM(CASE WHEN is_spider = 0 AND `is_automata` = 0 AND country NOT IN ("US", "Inv") AND `access_method` = "Mobile web" THEN pageviews END) AS ex_us_human_mobile,
  SUM(CASE WHEN is_spider = 0 AND `is_automata` = 0 AND country NOT IN ("US", "Inv") AND `refering_site` = "Google"  THEN pageviews END) AS ex_us_human_google,
  SUM(CASE WHEN is_spider = 0 AND `is_automata` = 0 AND country NOT IN ("US", "Inv") AND `access_method` = "Desktop" AND `refering_site` = "Google" THEN pageviews END) AS ex_us_human_desktop_google,
  SUM(CASE WHEN is_spider = 0 AND `is_automata` = 0 AND country NOT IN ("US", "Inv") AND `access_method` = "Mobile web" AND `refering_site` = "Google" THEN pageviews END) AS ex_us_human_mobile_google,
  SUM(CASE WHEN (is_spider = 1 OR `is_automata` = 1) AND country NOT IN ("US", "Inv") THEN pageviews END) AS ex_us_nonhuman
FROM `pentahoviews`
WHERE timestamp NOT IN ("2013-04-01", "2014-11-01")
GROUP BY 1;