title: Creating a materialized view for calculating histogram data
body: <p>I have created a table:</p>
  
  <pre><code>CREATE TABLE results
  (
      id UUID,
      date_time DateTime,
      item_id UInt32,
      value UInt16
  ) ENGINE = MergeTree()
  PARTITION BY toYYYYMMDD(date_time)
  ORDER BY (date_time, item_id);
  </code></pre>
  
  <p>And I want to create a materialized view to store hourly histogram data for value. For example;</p>
  
  <p>I expect an output like this:</p>
  
  <pre><code>toStartOfHour          item_id    value    count
  2019-12-18 00:00:00    1          0        4       /* number of rows with value between 0 and 100 and date_time between 2019-12-18 00:00:00 and 2019-12-18 01:00:00 */
  2019-12-18 00:00:00    1          100      7       /* number of rows with value between 100 and 200 and date_time between 2019-12-18 00:00:00 and 2019-12-18 01:00:00 */
  </code></pre>
  
  <p>Number of rows with <code>value</code> between 100 and 0, and <code>date_time</code> between <code>2019-12-18 00:00:00</code> and <code>2019-12-18 01:00:00</code>. I have tried something like this:</p>
  
  <pre><code>CREATE MATERIALIZED VIEW results_histogram_by_hour
  ENGINE = AggregatingMergeTree()
  PARTITION BY toYYYYMMDD(date_time)
  ORDER BY (date_time, item_id)
  POPULATE
  AS SELECT toStartOfHour(date_time) AS date_time,
            item_id,
            multiply(floor(value / 100), 100) AS value,
            countState() AS count
  FROM results
  GROUP BY date_time,
           item_id,
           value;
  </code></pre>
  
  <p>This materialized view definition works when populated. But with time and new rows, it gets wrong. How wrong? I don't know. I couldn't find a pattern.</p>
  
  <p>I am not sure if I found a bug on clickhouse or I am doing something wrong.</p>
  
  <p>Is my materialized view definition correct?</p>