title: QueryDsl aggregate by day of week as map
body: <p>I would like to get the number of bookings by type by day of week, e.g.:</p>
  <pre><code>| DoW   | Type   | Count |
  |-----  |--------|-------|
  | 1     | Store  | 3     |
  | 1     | Remove | 2     |
  </code></pre>
  <p>Result should be <code>Map&lt;Integer, Map&lt;String, Long&gt;&gt;</code>. I've tried this in some variations:</p>
  <pre><code>LocalDateTime monday = LocalDate.now().with(DayOfWeek.MONDAY).atStartOfDay();
  LocalDateTime nextMonday = monday.plusDays(7);
  
  var dayOfWeek = booking.bookingDate.dayOfWeek().subtract(1);
  
  new JPAQuery&lt;&gt;(entityManager)
                  .select(dayOfWeek, booking.bookingType, booking.bookingType.count())
                  .from(booking)
                  .where(booking.bookingDate.between(monday, nextMonday))
                  .groupBy(booking.bookingDate, booking.bookingType)
                  .transform(groupBy(dayOfWeek)
                          .as(map(booking.bookingType, booking.bookingType.count())));
  </code></pre>
  <p>It doesn't give me the correct counts though (everything 1). I thought about putting <code>dayofweek</code> in the groupby clause, bc. I was worried that grouping by LocalDateTime, the &quot;time&quot; part would mess things up, but that didn't work.</p>
  <p>This postgresql query works as expected:</p>
  <pre><code>select
      extract(DOW from b.book_date)-1 as dow,
      b.dtype as type,
      count(b.dtype) as cnt 
  from
      booking b 
  where
      b.book_date between '2020-05-11 0:0:0.000000' and '2020-05-18 0:0:0.000000'
  group by
      b.book_date,
      b.dtype
  </code></pre>
  <p><strong>EDIT:</strong></p>
  <p>When adding &quot;dayOfWeek&quot; to the groupBy clause, like this:</p>
  <pre><code>.groupBy(dayOfWeek, buchung.buchungType)
  </code></pre>
  <p>Then I get an Exception:</p>
  <pre><code>javax.persistence.PersistenceException: org.hibernate.exception.SQLGrammarException: could not extract ResultSet
  [...]
  Caused by: org.h2.jdbc.JdbcSQLSyntaxErrorException: Feld 
  
  &quot;BOOKING0_.BOOKING_DATE&quot; muss in der GROUP BY Liste sein
  Column &quot;BOOKING0_.BOOKING_DATE&quot; must be in the GROUP BY list; SQL statement:
  /* select dayofweek(booking.gebuchtDate) - ?1, booking.bookingType, count(booking.bookingType)
  from Buchung booking
  where booking.bookingDate between ?2 and ?3
  group by dayofweek(booking.gebuchtDate) - ?1, booking.bookingType */ select dayofweek(booking0_.booking_date)-? as col_0_0_, booking0_.dtype as col_1_0_, count(booking0_.dtype) as col_2_0_ from booking booking0_ where booking0_.booking_date between ? and ? group by dayofweek(booking0_.booking_date)-? , booking0_.dtype [90016-200]
  </code></pre>
  <p><strong>EDIT 2:</strong></p>
  <p>Jan-Willem Gmelig Meyling's answer below fixes the query, but my logic to convert Day of Week from Sun-Sat to Mon-Sun is of course rubbish. Let's have a look:</p>
  <p><a href="https://www.postgresql.org/docs/current/functions-datetime.html#FUNCTIONS-DATETIME-EXTRACT" rel="nofollow noreferrer">Postgresql</a> will return Sunday (0) to Saturday (6) (which seems to be the ISO SQL way), whereas <a href="https://docs.oracle.com/javase/8/docs/api/java/time/DayOfWeek.html" rel="nofollow noreferrer">java.time</a> returns ISO-8601 standard format from Monday (1) to Sunday (7). So basically everything matches up, except for Sunday. <br />
  To make matters worse, I've read that you can change the start day of the week on MS SQL.</p>
  <p>Since I don't want to bother with this headache, I've changed it to Day of Year and I'm converting in Java. Since the result set has at most 7 entries, this isn't so bad:</p>
  <pre><code>    var dayOfYear = booking.bookingDate.dayOfYear();
      
      var res = new JPAQuery&lt;&gt;(entityManager)
              .from(booking)
              .where(booking.bookingDate.between(monday, nextMonday))
              .groupBy(dayOfYear, booking.bookingType)
              .transform(groupBy(dayOfYear)
                      .as(map(booking.bookingType, booking.bookingType.count())));
  
      return res.entrySet().stream()
              .collect(toMap(
                      e -&gt; Year.now().atDay(e.getKey()).getDayOfWeek().getValue(),
                      Map.Entry::getValue
              ));
  </code></pre>