title: Create UDAF(not UDTF) in Snowflake
body: <p><code>Java UDFs return a scalar result. Java UDTFs are not currently supported.</code><a href="https://docs.snowflake.com/en/sql-reference/udf-overview.html#java" rel="nofollow noreferrer">reference</a></p>
  <p>That said, I created a Java UDF as given below</p>
  <pre class="lang-sql prettyprint-override"><code>CREATE OR replace function MAP_COUNT(colValue String)
  returns OBJECT 
  language java
  handler='Frequency.calculate'
  target_path='@~/Frequency.jar'
  as
  $$
      import java.util.HashMap;
      import java.util.Map;
      import java.util.Optional;
      class Frequency {
          Map&lt;String, Integer&gt; frequencies = new HashMap&lt;&gt;();
  
          public Map&lt;String, Integer&gt; calculate(String colValue) {
              frequencies.putIfAbsent(colValue, 0);
              frequencies.computeIfPresent(colValue, (key, value) -&gt; value + 1);
              return frequencies;
          }
      }
  $$;
  
  </code></pre>
  <p>Using <code>MAP_COUNT</code> UDF in a query as below</p>
  <pre class="lang-sql prettyprint-override"><code>with temp_1 as
  (
      SELECT 'John' AS my_col, 27 as age
      UNION ALL
      SELECT 'John' AS my_col, 28 as age
      UNION ALL
      SELECT 'doe' AS my_col, 27 as age
      UNION ALL
      SELECT 'doe' AS my_col, 28 as age
  )
  select  MAP_COUNT(a.my_col) from temp_1 a;
  </code></pre>
  <p>I get result as below</p>
  <pre><code>|MAP_COUNT(A.MY_COL)            |
  |-------------------------------|
  |{  &quot;John&quot;: &quot;1&quot; }               |
  |{  &quot;John&quot;: &quot;2&quot; }               |
  |{ &quot;John&quot;: &quot;2&quot;,  &quot;doe&quot;: &quot;1&quot; }   |
  |{  &quot;John&quot;: &quot;2&quot;,  &quot;doe&quot;: &quot;2&quot;}   |
  </code></pre>
  <p>The result I expect from my UDF is as below</p>
  <pre><code>|MAP_COUNT(A.MY_COL)            |
  |-------------------------------|
  |{  &quot;John&quot;: &quot;2&quot;,  &quot;doe&quot;: &quot;2&quot;}   |
  </code></pre>
  <p><strong>Is it possible in snowflake?</strong></p>
  <p>What if I have query like below?</p>
  <pre class="lang-sql prettyprint-override"><code>with temp_1 as
  (
      SELECT 'John' AS my_col, 27 as age
      UNION ALL
      SELECT 'John' AS my_col, 28 as age
      UNION ALL
      SELECT 'doe' AS my_col, 27 as age
      UNION ALL
      SELECT 'doe' AS my_col, 28 as age
  )
  select  MAP_COUNT(a.my_col) as names, MAP_COUNT(a.age) as ages  from temp_1 a;
  </code></pre>
  <p>The result I expect from my UDF is as below</p>
  <pre><code>|names                          ||AGES                           |
  |-------------------------------||-------------------------------|
  |{  &quot;John&quot;: &quot;2&quot;,  &quot;doe&quot;: &quot;2&quot;}   ||{  &quot;27&quot;: &quot;2&quot;,  &quot;28&quot;: &quot;2&quot;}      |
  </code></pre>
  <p>There are ways to achieve this by simply restructuring query but I want to know if it is possible to do by using <code>MAP_COUNT</code> function similar to <code>OBJECT_AGG</code> function in select clause.</p>