쿼리질문입니다...

Question

해당값을 합치고 싶어서요렇게 하구 이렇게 해도 안됩니다... 조언좀 부탁 드려요 ㅜSQL Error [1111] [HY000]: (conn=434421) Invalid use of group function오류는 이게 뜹니다 ㅜㅜ

student mr · Answer

일단 쿼리 자체를 고칠수는 없나요?만약에 쿼리 자체를 수정할 수 있다면... SUM()을 CASE 내부에서 직접 사용하지 말고, 개별 조건을 SUM()으로 감싸보시죠. SUM( CASE WHEN onsitemaster0_.dept2code = 'BB000' AND onsitemaster1_.status > '30' AND onsitemaster3_.status > '40' THEN 1 ELSE 0 END ) AS total만약 쿼리를 못고친다면... 하위 코드로 수정해보세요.NumberExpression<Long> gmingExpr = new CaseBuilder().when(onsiteMasterWork.dept2code.eq("BB000").and(notCompletedItemsExpr.gt(0))).then(1L).otherwise(0L);return jpaQueryFactory().selectDistinct(Projections.bean(OnsiteMasterWorkDto.class,gmingExpr.sum().as("gming") // 이부분에 한번 추가해보시죠.)).from(onsiteMasterWork).leftJoin(onsiteMasterWorkDeviceMapping).on(onsiteMasterWork.onsiteMasterNo.eq(onsiteMasterWorkDeviceMapping.onsiteMasterNo)).leftJoin(deviceMaster).on(deviceMaster.uniqueNo.eq(onsiteMasterWorkDeviceMapping.uniqueNo)).leftJoin(onsiteMasterWorkPowerStationMapping).on(onsiteMasterWork.onsiteMasterNo.eq(onsiteMasterWorkPowerStationMapping.onsiteMasterNo)).leftJoin(onsiteMasterWorkUnregister).on(onsiteMasterWork.onsiteMasterNo.eq(onsiteMasterWorkUnregister.onsiteMasterNo)).leftJoin(powerStation).on(onsiteMasterWorkPowerStationMapping.locationCode.eq(powerStation.locationCode)).where(onsiteMasterWork.useYn.eq("Y")).groupBy(onsiteMasterWork.onsiteMasterNo).fetch();

쿼리질문입니다...

1개의 답변

Q&A 목록