I want to make this SQL to QueryDsl, what should I do?

Question

i made SQL statistics by date.

this means total count of the visitors by date.

select sum(r.count) from (SELECT DATE_FORMAT(reg_date,'%Y-%m-%d') m, COUNT(distinct client_ip) as count FROM request_log GROUP BY m) as r;

following is the result of this SQL [enter image description here][1]
So, i want to exchange this for QueryDSL. I read the following documents and tried these. http://www.querydsl.com/static/querydsl/3.6.3/reference/ko-KR/html_single/#alias

@Transactional
@SpringBootTest
public class QueryDslTest {
    
    @PersistenceContext
    EntityManager em;
    
    @Test
    @DisplayName("QueryDSL_TEST")
    public void testQuerydsl() throws Exception {
        JPAQueryFactory queryFactory = new JPAQueryFactory(em);
        
        QRequestLog requestLog = new QRequestLog("requestLog");
        
        StringTemplate dateFormat = Expressions.stringTemplate(
                "DATE_FORMAT({0}, {1})"
                , requestLog.regDate
                , ConstantImpl.create("%Y-%m-%d"));
    
        JPQLQuery<Long> countDistinct = JPAExpressions.select(requestLog.clientIp.countDistinct());
    
                QueryResults<Tuple> count = queryFactory
                        .select(requestLog.regDate, requestLog.clientIp.countDistinct().as("count"))
                        .from(requestLog)
                        .groupBy(dateFormat, requestLog.regDate)
                        .fetchResults();
        
//                Long fetch = queryFactory
//                        .select(Projections.fields(Long.class, ExpressionUtils.as(
//                                select(requestLog.regDate, requestLog.clientIp.countDistinct().as("c"))
//                                        .from(requestLog)
//                                        .groupBy(dateFormat, requestLog.regDate), "r")
//                                                  ))
//                        .from(requestLog)
//                        .fetchOne();
        
//        JPAQuery<Tuple> tupleJPAQuery = queryFactory
//                .select(dateFormat, countDistinct)
//                .from(requestLog)
//                .groupBy(dateFormat);
//
//        queryFactory
//                .select(tupleJPAQuery)
        
        System.out.println("count = " + count);
        
//                queryFactory
//                        .select(requestLog)
//                        .from(
//                                select(requestLog.regDate.as("d"), requestLog.clientIp.countDistinct().as("c"))
//                                .from(requestLog)
//                                .groupBy(requestLog.as("d"))
//                             )
    }
    
}```

I don't know how to use an subquery alias. 

Can you give me a hint?

  [1]: https://i.sstatic.net/WUMkl.jpg

Answer 1

I used the date function of mysql. This is not date_format function !

but I still don't know how to explain the subquery.

so, I used select query and sum of java stream.

I still feel like I need to study more.

@Override
@Transactional(readOnly = true)
public Long allVisitors() {
        JPAQueryFactory queryFactory = new JPAQueryFactory(getEntityManager());
        return queryFactory
                .select(requestLog.clientIp.countDistinct())
                .from(requestLog)
                .groupBy(functionDate(requestLog.regDate))
                .fetch()
                .stream()
                .reduce(0L, Long::sum);
}

private StringTemplate functionDate(DateTimePath regDate) {
        return stringTemplate("date({0})", regDate);
}