Get in touch

Download Plugin ↗
WP Data Access

Demo – Pivot Query

Z

Demonstrated Features

  • Chart
  • Custom Chart Options

Required Skills

  • Pivot Query

Required License

  • Premium

Insights

Custom options were applied to reduce the white space around the chart (docs). The chart query can be edited in the App Manager (docs).

SQL Query

SELECT
    DATE_FORMAT(date_utc, '%d-%b') AS `Day`,
    ROUND(AVG(CASE WHEN YEAR(date_utc) = 2026 THEN lake_level_cm ELSE 0 END), 2) AS `2026`,
    ROUND(AVG(CASE WHEN YEAR(date_utc) = 2025 THEN lake_level_cm ELSE 0 END), 2) AS `2025`,
    ROUND(AVG(CASE WHEN YEAR(date_utc) = 2024 THEN lake_level_cm ELSE 0 END), 2) AS `2024`,
    ROUND(AVG(CASE WHEN YEAR(date_utc) = 2023 THEN lake_level_cm ELSE 0 END), 2) AS `2023`,
    ROUND(AVG(CASE WHEN YEAR(date_utc) = 2022 THEN lake_level_cm ELSE 0 END), 2) AS `2022`,
    ROUND(AVG(CASE WHEN YEAR(date_utc) = 2021 THEN lake_level_cm ELSE 0 END), 2) AS `2021`,
    ROUND(AVG(CASE WHEN YEAR(date_utc) = 2020 THEN lake_level_cm ELSE 0 END), 2) AS `2020`,
    ROUND(AVG(CASE WHEN YEAR(date_utc) = 2019 THEN lake_level_cm ELSE 0 END), 2) AS `2019`,
    ROUND(AVG(CASE WHEN YEAR(date_utc) = 2018 THEN lake_level_cm ELSE 0 END), 2) AS `2018`,
    ROUND(AVG(CASE WHEN YEAR(date_utc) = 2017 THEN lake_level_cm ELSE 0 END), 2) AS `2017`,
    ROUND(AVG(CASE WHEN YEAR(date_utc) = 2016 THEN lake_level_cm ELSE 0 END), 2) AS `2016`,
    ROUND(AVG(CASE WHEN YEAR(date_utc) = 2015 THEN lake_level_cm ELSE 0 END), 2) AS `2015`,
    ROUND(AVG(CASE WHEN YEAR(date_utc) = 2014 THEN lake_level_cm ELSE 0 END), 2) AS `2014`,
    ROUND(AVG(CASE WHEN YEAR(date_utc) = 2013 THEN lake_level_cm ELSE 0 END), 2) AS `2013`
FROM
    `lake_info`
WHERE
    YEAR(date_utc) BETWEEN 2013 AND 2026
GROUP BY
    DATE_FORMAT(date_utc, '%d-%b')
ORDER BY
    DAYOFYEAR(date_utc) ASC

Options

{
  "chartArea": {
    "top": 110,
    "left": 80,
    "right": 80,
    "bottom": 80
  }
}

Unlock the possibilities of your WordPress dashboard

Get Premium