hive group and count example

gistfile1.txt

SELECT
  month,
  day,
 SUM(CASE WHEN (user_agent LIKE '%iPhone%') THEN 1 ELSE 0 END) AS iphone,
 SUM(CASE WHEN (user_agent LIKE '%iOS%') THEN 1 ELSE 0 END) AS iOS
FROM wmf.webrequest
WHERE webrequest_source = 'text'
  AND year = 2016
  AND month IN (9, 10)
  AND (user_agent like '%iOS%' OR user_agent like '%iPhone%')
  AND (user_agent like '%Wikipedia/5.0%' OR user_agent like '%WikipediaApp%')
GROUP BY
  month,
  day
ORDER BY
  month,
  day
LIMIT 1000;