Click here to Skip to main content
15,868,016 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
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
Posted
Updated 4-Mar-20 19:51pm
Comments
MarcusCole6833 5-Mar-20 11:36am    
Going off what was said earlier, if you know your data could you break it down into smaller parts passing between temp table or ctes and code it out on a step by step basis? The reason I say this is the large group by (as stated) and te mass of column values derived by sub query's.

1 solution

We can't help you directly based on a query like that - we have no idea what your data looks like, or what you expect to get from it; but whenever I see a GROUP BY with a long list of fields, it's probably wrong. Each time you add a field to a GROUP BY clause, you add top the number of rows you generate, not reduce them. I think you want to think hard about your data and exactly what you are trying to fetch before you start writing a query!

This may help: SQL GROUP By and the "Column 'name' is invalid in the select list because..." error[^]
 
Share this answer
 
Comments
Member 12004926 5-Mar-20 1:54am    
the main issue is how do i add subquery column in group by

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900