how do i write a query in oracle 11 g with group by in subquery
my query is
;WITH cte(select
distinct
KioskStatus.StatusDate,
zonedetails.ZoneId,
zonedetails.ZoneName,
zonedetails.zonecode,
branchdetails.branchname,
branchdetails.branchcode,
KioskInformations.KioskId,
KioskInformations.IPAddress,
KioskStatus.Connected,
TO_char(KioskStatus.StatusDate ,'DD-MM-YYYY') StartDate,
TO_char(KioskStatus.StatusDate ,'HH24:mi:SS') StartTime,
min(KioskStatus.StatusDate) mindate,
max(KioskStatus.StatusDate) LastStatus,
(select Count(KioskId) from NothingToPrints where TO_char(StatusDate,'yyyy-mm-dd')=TO_char(KioskStatus.StatusDate,'yyyy-mm-dd') and KioskId=KioskStatus.KioskId) TotalTrn,
(select Count(KioskId) from barcodeerrors where TO_char(StatusDate,'yyyy-mm-dd')=TO_char(KioskStatus.StatusDate,'yyyy-mm-dd') and KioskId=KioskStatus.KioskId) TotalTrn1,
(select Count(KioskId) from NothingToPrints where TO_char(StatusDate,'yyyy-mm-dd')=TO_char(KioskStatus.StatusDate,'yyyy-mm-dd') and StatusMessage='Printing Success' and KioskId=KioskStatus.KioskId) SuccessTransaction,
(select Count(KioskId) from NothingToPrints where TO_char(StatusDate,'yyyy-mm-dd')=TO_char(KioskStatus.StatusDate,'yyyy-mm-dd') and StatusMessage='Noting to print' and KioskId=KioskStatus.KioskId) NotingtoPrint,
(select Count(KioskId) from NothingToPrints where TO_char(StatusDate,'yyyy-mm-dd')=TO_char(KioskStatus.StatusDate,'yyyy-mm-dd')and KioskId=KioskStatus.KioskId) /30 AvgTrn
FROM
KioskInformations
LEFT JOIN KioskStatus ON KioskInformations.KioskId = KioskStatus.KioskId
INNER JOIN ZoneDetails ON KioskInformations.ZoneId = ZoneDetails.ZoneId
INNER JOIN BranchDetails ON KioskInformations.BranchId = BranchDetails.BranchId AND ZoneDetails.ZoneId = BranchDetails.ZoneId
group by
KioskStatus.StatusDate,
zonedetails.ZoneId,
zonedetails.ZoneName,
zonedetails.zonecode,
branchdetails.branchname,
branchdetails.branchcode,
KioskInformations.KioskId,
KioskInformations.IPAddress,
KioskStatus.Connected,
TO_char(KioskStatus.StatusDate ,'DD-MM-YYYY') ,
TO_char(KioskStatus.StatusDate ,'HH24:mi:SS') )
What I have tried:
adding aggrecate colum in where clause