Query library

Welcome to the SQL query library for ClickHouse.

Introduction

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;

Aggregation of data items by quarterly periods and region org unit level

select
  "PT Quarterly",
  "OU Region",
  "DataItem",
  sum("Value") as "Value"
from
  "myschema"."data" ax
where
  "DataItem" like '%TX_CURR%'
  and "PT Yearly" in ('2024Q1', '2024Q2', '2024Q3', '2024Q4')
group by
  "PT Quarterly",
  "OU Region",
  "DataItem"

Aggregation of data items for last 5 years order by aggregate value

select
  "PT Quarterly",
  "OU Region",
  "DataItem",
  sum("Value") as "Value"
from
  "myschema"."mybase"
where
  "DataItem" like '%TX_CURR%'
  and "PT Yearly" in (
    select
      "ID"
    from
      "myschema"."mybase_relative_yearly_period"
    where
      "Index" <= 5
  )
group by
  "PT Quarterly",
  "OU Region",
  "DataItem"
order by
  "Value" desc