title: How to write HQL-query?
body: <p>There is a table <code>CART_ITEMS</code>, where <code>drink_id</code> is the <code>id</code> of the drink, and <code>order_id</code> is the <code>id</code> of the order in which it is located, <code>count</code> is the amount of this drink in the order. You need to display the 6 most popular drinks from this table. I wrote the SQL query:</p>
  <pre class="lang-sql prettyprint-override"><code>SELECT drink_id, sum(count) as count
  FROM cart_items
  GROUP BY drink_id
  ORDER BY 2 DESC
  LIMIT 6
  </code></pre>
  <p>But how can we rewrite it in <code>HQL</code> now? I tried to write code, but the Intelij Idea swears at the <code>sum</code> and <code>limit</code> functions.</p>
  <pre class="lang-java prettyprint-override"><code>@Query(&quot;select C.drink, sum(count) as count from CartItem C group by C.drink order by 2 DESC limit 6&quot;)
      List&lt;Drink&gt; getMostPopularDrinks();
  </code></pre>
  <p>CartItem:</p>
  <pre class="lang-java prettyprint-override"><code>@Getter
  @Setter
  @Entity
  @NoArgsConstructor
  @Table(name = &quot;cart_items&quot;)
  public class CartItem {
  
      @Data
      @Embeddable
      @NoArgsConstructor
      @AllArgsConstructor
      public static class CartId implements Serializable {
  
          private Long order_id;
  
          private Long drink_id;
      }
  
  
      /**
       * key @EmbeddedId
       */
      @EmbeddedId
      private CartId cartId;
  
      @ManyToOne
      @JoinColumn(name = &quot;order_id&quot;, referencedColumnName = &quot;id&quot;,
              nullable = false, insertable = false, updatable = false)
      private Order order;
  
      @ManyToOne
      @JoinColumn(name = &quot;drink_id&quot;, referencedColumnName = &quot;id&quot;,
              nullable = false, insertable = false, updatable = false)
      private Drink drink;
  
      /**
       * Count of drink
       */
      private int count;
  }
  </code></pre>
  <p><a href="https://i.stack.imgur.com/uaUb2.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/uaUb2.png" alt="введите сюда описание изображения" /></a></p>