Use GROUP BY clause in Oracle for calculating average

zeeshanpas
 
on May 04, 2022 10:52 PM
720 Views

Hi,i am trying to do group by  by multiple columns in oracle procedure but i should put all the columns in group by watever it is present in select query

Do all columns in a SELECT list have to appear in a GROUP BY clause?

How would i write the query to mention only the particular columns whichever it is require in group by clause?if i dont put all the columns in group by am getting error like group by not an expression

  SELECT 
                ses.session_id,
                ( ss.participant_status ) status,
                nvl(
                    tf.lesson_objective,
                    ses.lesson_objective
                ) lesson_objective,
                ses.question,
             TO_CHAR(
                    new_time(
                        ses.scheduled_starttime,
                        'GMT',
                        'EST'
                    ),
                    'MM/DD/YYYY hh:mi:ss am'
                )  scheduled_starttime,
                DECODE(
                    ss.participant_status,
                    0,
                    'Created',
                    1,
                    'On-Goining',
                    2,
                    'Completed',
                    3,
                    'Student Canceled',
                    4,
                    'Student Missed',
                    5,
                    'Tutor Missed',
                    6,
                    'Admin Delete',
                    7,
                    'Admin Cancel',
                    9,
                    'Orientation Completed',
                    10,
                    'Completed - Unsuccessful',
                    'NULL'
                ) session_status,
                DECODE(
                    nvl(
                        ses.session_type,
                        0
                    ),
                    1,
                    'Group',
                    'Single'
                ) session_type,
                DECODE(
                    ses.type,
                    0,
                    'On-Demand',
                    1,
                    'Admin Schedule',
                    2,
                    'Student SCHEDULED',
                    'NA'
                ) type,
                ses.subject_id,
                ses.subject_name,
                stugrade.grade_name AS studentgrade,
                ss.student_id,
                st.referrence_id AS studentreferenceid,
                st.first_name AS studentfirstname,
                st.middle_name AS studentmiddlename,
                st.last_name AS studentlastname,
                stul.user_name AS studentusername,
                  TO_CHAR(
                    new_time(
                        ses.actual_endtime,
                        'GMT',
                        'EST'
                    ),
                    'MM/DD/YYYY hh:mi:ss am'
                ) AS actual_endtime,
                tut.tutor_id,
                tut.first_name AS tutorfirstname,
                tut.last_name AS tutorlastname,
                tut.tutoring_center,
                tutl.user_name AS tutorusername,
                ses.fqst_actual_time,
                ses.school_id,
                sc.name AS schoolname,
                ss.student_wait_time,
                ses.tutor_activity_avg_res_time,
                ses.session_question_loaded_time,
                sf.session_rating,
                sf.tutor_rating,
                di.district_id,
                di.district_name,
               nvl(
                    TO_CHAR(
                    new_time(
                        ses.actual_starttime,
                        'GMT',
                        'EST'
                    ),
                    'MM/DD/YYYY hh:mi:ss am'
                ),
                    TO_CHAR(
                    new_time(
                        ses.scheduled_starttime,
                        'GMT',
                        'EST'
                    ),
                    'MM/DD/YYYY hh:mi:ss am'
                )
                ) AS starttime,
                nvl(
                    TO_CHAR(
                    new_time(
                        ses.actual_endtime,
                        'GMT',
                        'EST'
                    ),
                    'MM/DD/YYYY hh:mi:ss am'
                ),
                    TO_CHAR(
                    new_time(
                        ses.scheduled_endtime,
                        'GMT',
                        'EST'
                    ),
                    'MM/DD/YYYY hh:mi:ss am'
                )
                ) AS endtime,
                 TO_CHAR(
                    new_time(
                        ss.joined_time,
                        'GMT',
                        'EST'
                    ),
                    'MM/DD/YYYY hh:mi:ss am'
                ) AS studentjointime,
                  TO_CHAR(
                    new_time(
                        ss.quit_time,
                        'GMT',
                        'EST'
                    ),
                    'MM/DD/YYYY hh:mi:ss am'
                ) AS studentquittime,
                nvl(
                    sc.is_test_data,
                    0
                ) AS istestsession,
                tf.participation_points,
                tf.progress_notes,
                tf.tutor_comments,
                round(
                    (nvl(
                        ses.actual_endtime,
                        ses.scheduled_endtime
                    ) - nvl(
                        ses.actual_starttime,
                        ses.scheduled_starttime
                    ) ) * 24 * 60,
                    4
                ) AS actualduration,
                round(
                    (ses.scheduled_endtime - ses.scheduled_starttime) * 24 * 60,
                    4
                ) AS scheduleduration,
                ses.billable_duration,
                ses.recording_url,
                ses.chat_script_url,
                ( fpg.name ) program_name,
                pgv.version_name,
                ( sf.comments ) student_comments,
                fevusr.first_name || ' ' || fevusr.last_name AS TEACHERNAME,
                sta.state_name AS STATE,
                ss.device AS Device_Type,
                ss.browser AS Browser_Type,
                decode(ses.schedule_selection_type,0,'Course Work',1,'My Learning Plan',
                                    2,'Orientations',3,'Booster',4,
                                    'Curriculum Help',5,'ETI')Category,
               fevsesstag.value AS Curriculum,
               ses.program_id,
               tf.exit_ticket_attempted    AS exit_ticket_attempted,
               tf.exit_ticket_earned       AS exit_ticket_earned,
               tf.exit_ticket_score        AS exit_ticket_score,
               tf.assessment_readiness AS AssessmentReady,
               GREATEST(round((ses.actual_endtime -  ses.actual_starttime)* 24 * 60,
                    4),round((ses.scheduled_endtime -  ses.scheduled_starttime)* 24 * 60,
                    4)) AS session_billable_duration

               
                
            FROM
                fev_oltsession ses
                LEFT OUTER JOIN fev_student_oltsession ss ON ses.session_id = ss.session_id
                LEFT OUTER JOIN fev_school sc ON ses.school_id = sc.school_id
                LEFT OUTER JOIN fev_district di ON sc.district_id = di.district_id
                LEFT OUTER JOIN fev_oltsession_tutor_feedback tf ON (
                        ses.session_id = tf.session_id
                    AND
                        ss.student_id = tf.student_id
                )
                LEFT OUTER JOIN fev_oltsesson_student_feedback sf ON (
                        ses.session_id = sf.session_id
                    AND
                        ss.student_id = sf.student_id
                )
               
                LEFT OUTER JOIN fev_student st ON ss.student_id = st.student_id
                LEFT OUTER JOIN fev_state sta ON st.state_id = sta.state_id
                LEFT OUTER JOIN fev_login stul ON st.login_id = stul.login_id
                LEFT OUTER JOIN fev_student_teacher studttcher ON st.student_id = studttcher.student_id
                 AND  studttcher.status = 1
                                                                                     
                LEFT OUTER JOIN fev_user fevusr ON fevusr.user_id = studttcher.teacher_id
                LEFT OUTER JOIN fev_session_tag_detail fevsesstag ON fevsesstag.session_id = ses.session_id
                LEFT OUTER JOIN fev_tutor_oltsession ts ON (
                        ses.session_id = ts.session_id
                    AND
                        ts.transfered_from IS NULL
                )
                LEFT OUTER JOIN fev_tutor tut ON ts.tutor_id = tut.tutor_id
                LEFT OUTER JOIN fev_login tutl ON tut.login_id = tutl.login_id
                
                LEFT OUTER JOIN fev_grade stugrade ON st.grade_id = stugrade.grade_id
                LEFT OUTER JOIN fev_program fpg ON (
                    fpg.program_id = ses.program_id
                )
                LEFT OUTER JOIN fev_program_version pgv ON (
                    pgv.version_id = ses.version_id
                )
               
            WHERE
                  
                       ses.scheduled_starttime >= TO_DATE(
                        '02/04/2022 04:00:00 AM',
                        'mm/dd/yyyy hh:mi:ss am'
                    )
                AND
                    ses.scheduled_endtime < TO_DATE(
                        '03/05/2022 04:00:00 AM',
                        'mm/dd/yyyy hh:mi:ss am'
                    )
                AND
                    nvl(
                        sc.is_test_data,
                        0
                    ) != 1
                AND
                    ses.school_id NOT IN (
                        213,358
                    )
                AND
                    ses.status IS NOT NULL
                    AND
                    ses.session_type =1
          
                   GROUP BY ses.session_id,ss.student_id,ses.session_type
                  --  GROUP BY
                                    --    ROLLUP(ses.session_id,ss.student_id,ses.session_type);
              

Above is my query 

Download FREE API for Word, Excel and PDF in ASP.Net: Download
arjunv
 
on May 05, 2022 12:15 AM
on May 05, 2022 12:15 AM

Hi zeeshanpas,

When using GROUP BY clause you need to declare all the columns in your SELECT query except the Aggregate columns.

Check this example. Now please take its reference and correct your code.

Query

SELECT TOP 2 ProductId
    ,ProductName
    ,AVG(UnitPrice) UnitPrice
FROM Products
GROUP BY ProductId, ProductName

Output