techonology_counts_by_location.sql

select location, count(distinct(org_id)) as total_org_count
      ,sum(sha256) as sha256
      ,sum(amazon_ecs_agent) as amazon_ecs_agent
      ,sum(postgres) as postgres
      ,sum(mongo) as mongo
      ,sum(mysql) as mysql
      ,sum(nginx) as nginx
      ,sum(elasticsearch) as elasticsearch
      ,sum(heapster) as heapster
      ,sum(ubuntu) as ubuntu
      ,sum(rabbitmq) as rabbitmq
      ,sum(cadvisor) as cadvisor
      ,sum(metadata) as metadata
      ,sum(dns) as dns
      ,sum(healthcheck) as healthcheck
      
      
    from ( select location, dh.org_id as org_id
      , max(CASE when image_names ilike '%sha256%'  THEN 1 ELSE 0 END) as sha256
      , max(CASE when image_names ilike '%amazon-ecs-agent%'  THEN 1 ELSE 0 END) as amazon_ecs_agent
      , max(CASE when image_names ilike '%postgres%'  THEN 1 ELSE 0 END) as postgres
      , max(CASE when image_names ilike '%mongo%'  THEN 1 ELSE 0 END) as mongo
      , max(CASE when image_names ilike '%mysql%'  THEN 1 ELSE 0 END) as mysql
      , max(CASE when image_names ilike '%nginx%'  THEN 1 ELSE 0 END) as nginx
      , max(CASE when image_names ilike '%elasticsearch%'  THEN 1 ELSE 0 END) as elasticsearch
      , max(CASE when image_names ilike '%heapster%'  THEN 1 ELSE 0 END) as heapster
      , max(CASE when image_names ilike '%ubuntu%'  THEN 1 ELSE 0 END) as ubuntu
      , max(CASE when image_names ilike '%rabbitmq%'  THEN 1 ELSE 0 END) as rabbitmq
      , max(CASE when image_names ilike '%cadvisor%'  THEN 1 ELSE 0 END) as cadvisor
      , max(CASE when image_names ilike '%metadata%'  THEN 1 ELSE 0 END) as metadata
      , max(CASE when image_names ilike '%dns%'  THEN 1 ELSE 0 END) as dns
      , max(CASE when image_names ilike '%healthcheck%'  THEN 1 ELSE 0 END) as healthcheck
      from datamart.dim_host dh
      join datamart.fact_host_integration fhi on fhi.host_id = dh.id
/*       where location = 'AWS' */
      group by location, dh.org_id
      )
      group by 1