title: Getting the historical data feature on Cyfe to work with multiple MySQL GROUP BY variables
body: <p>I'm trying to create a MySQL widget on Cyfe that shows the most commonly recommended products for users. The complication is that I need to group the data by Date as well, because I want to take advantage of the &quot;historical data function&quot; on Cyfe. I'm having trouble getting this historical data feature on Cyfe to work.</p>
  <p>Here is an example of what my data looks like:</p>
  <div class="s-table-container">
  <table class="s-table">
  <thead>
  <tr>
  <th>date_created</th>
  <th>rec_prod_1</th>
  </tr>
  </thead>
  <tbody>
  <tr>
  <td>2021-12-15 01:09:58</td>
  <td>402</td>
  </tr>
  <tr>
  <td>2021-12-14 22:21:19</td>
  <td>52</td>
  </tr>
  <tr>
  <td>2021-12-14 21:53:29</td>
  <td>1155</td>
  </tr>
  <tr>
  <td>2021-12-14 19:21:32</td>
  <td>52</td>
  </tr>
  <tr>
  <td>2021-12-14 18:59:24</td>
  <td>52</td>
  </tr>
  <tr>
  <td>2021-12-14 16:53:07</td>
  <td>1155</td>
  </tr>
  </tbody>
  </table>
  </div>
  <p>rec_prod_1 contains the ID's of the recommended products.</p>
  <p>I want to create a table on Cyfe where the data is grouped by frequency of rec_prod_1 and ordered in descending order of frequency:</p>
  <div class="s-table-container">
  <table class="s-table">
  <thead>
  <tr>
  <th>rec_prod_1</th>
  <th>freq</th>
  </tr>
  </thead>
  <tbody>
  <tr>
  <td>52</td>
  <td>3</td>
  </tr>
  <tr>
  <td>1155</td>
  <td>2</td>
  </tr>
  <tr>
  <td>402</td>
  <td>1</td>
  </tr>
  </tbody>
  </table>
  </div>
  <p>But, to take advantage of the &quot;historical data function&quot; on Cyfe I need to group the data by Date as well. This would allow me to see the highest product recommendations for today, the past 7 days, etc. by clicking on the buttons at the top of the website (<a href="https://www.cyfe.com/" rel="nofollow noreferrer">https://www.cyfe.com/</a>). Cyfe gives advice on how to add this code to your SQL code in the following link: (<a href="https://help.cyfe.com/cyfe/s/article/SQL" rel="nofollow noreferrer">https://help.cyfe.com/cyfe/s/article/SQL</a>)</p>
  <p>What I want to see on Cyfe is a table that is ordered by the frequency of recommended products that changes data output depending on what dates are chosen using the historical data function.</p>
  <p>Here is what I tried:</p>
  <pre><code>SELECT date_format(date_created, '%Y%m%d') AS Date, rec_prod_1, count(*) AS freq 
  FROM quiz 
  GROUP BY rec_prod_1, Date 
  ORDER BY Date DESC;
  </code></pre>
  <p>I also tried this:</p>
  <pre><code>SELECT date_format(date_created, '%Y%m%d') AS Date, rec_prod_1, count(*) AS freq 
  FROM quiz 
  GROUP BY rec_prod_1, Date
  ORDER BY freq DESC;
  </code></pre>
  <p>The lines</p>
  <pre><code>SELECT date_format(date_created, '%Y%m%d') AS Date
  </code></pre>
  <p>and</p>
  <pre><code>GROUP BY Date
  </code></pre>
  <p>are required to access the historical data function on Cyfe.</p>
  <p>However, neither of these chunks of code responded to the &quot;historical data function&quot;. For e.g., when I click on &quot;today&quot;, I still get data from other days.</p>
  <p>This is when the chart type of the SQL widget on Cyfe is set to &quot;List&quot;. When I change the chart type to &quot;Table&quot;, the historical data function works, but the product ID's for each day get summed, e.g., The product ID for Dec 14th in this example would be: 2466</p>
  <p>Is it possible to write MySQL code that would group the data by frequency of recommended products while also grouping the data by Date in a way that would respond to Cyfe's historical data feature without summing the product IDs?</p>
  <p>Thank you!</p>