Query library

Welcome to the SQL query library for ClickHouse. The schema myschema and base table name mybase in queries must be updated to match the relevant environment.

DHIS2

Data elements with data set membership

select
  de."Name" as "DataElementName",
  arrayStringConcat(groupArray(ds."Name"), ', ') as "DataSetName"
from
  "myschema"."mybase_datasetelement" dse
  inner join "myschema"."mybase_dataset" ds on dse."DataSetID" = ds."ID"
  inner join "myschema"."mybase_dataelement" de on dse."DataElementID" = de."ID"
group by
  de."Name"
order by
  de."Name";

Data value count by data element

select
  de."Name" as "DataElementName",
  ds."Name" as "DatasetName",
  count(*) as "DataValueCount"
from
  "myschema"."mybase_datavalue" dv
  inner join "myschema"."mybase_dataelement" de on dv."DataElementID" = de."ID"
  inner join "myschema"."mybase_datasetelement" dse on de."ID" = dse."DataElementID"
  inner join "myschema"."mybase_dataset" ds on dse."DataSetID" = ds."ID"
group by
  de."Name",
  ds."Name"
order by
  "DataValueCount" desc;