grouping while querying oracle database

Question

Below is the query that i am using. but

Query:

SELECT   c.session_id     session_number, 
         u.first_name     first_name, 
         u.last_name      last_name, 
         Min(c.timestamp) session_start_ts, 
         Max(c.timestamp) session_end_ts, 
         Count( 
         CASE 
                  WHEN c.message_type='IN' THEN 1 
                  ELSE NULL 
         END)                  message_in_count, 
         Count(c.message_type) total_messages 
FROM     users u 
join     chatlog c 
ON       u.user_id = c.user_id 
WHERE    Trunc(c.timestamp) BETWEEN To_date('2017-10-11','YYYY-MM-DD') AND      To_date('2017-11-09','YYYY-MM-DD') 
GROUP BY c.session_id, 
order by timestamp;

The problem is that it gives an error stating "not a GROUP BY expression". But instead of just grouping by session id if i use :

 group by c.session_id, u.first_name, u.last_name, c.timestamp

It works though the values of first_name and last_name are same for a particulat session_id and timestamp also i am only taking the max. so cant understand why i am unable to group by session_id only.

You can try this `group by session_number, first_name, last_name` It will work. if it is not work than post your error message. — NikuNj Rathod, Nov 09 '17 at 07:09
@NikuNjRathod it would be similar to what i mentioned that by adding last_name and first_name in group by. the query is working. but i just want to group it by session_id only. and secondly using just session_number instead of c.session_id it gives error stating :" "SESSION_NUMBER": invalid identifier" — Srijan Sharma, Nov 09 '17 at 07:14
Please visit this link for your solution URL : https://stackoverflow.com/questions/6424092/how-to-use-group-by-clause-when-we-use-aggregate-function-in-the-joins — NikuNj Rathod, Nov 09 '17 at 07:27

APC · Answer 1 · 2017-11-09T10:11:28.253

"cant understand why i am unable to group by session_id only"

The rule of Oracle aggregation functions is that we need to group by all the non-aggregated columns in the projection. In your case that is

group by c.session_id, u.first_name, u.last_name

" how do we overcome that. i mean there must be a way that you can overcome that. so that if you want to select multiple columns and just group by a particular column?"

This doesn't apply in your case. You say:

"the values of first_name and last_name are same for a particulat (sic) session_id"

which means grouping by session_id,first_name,last_name is the same as grouping by session_id alone. But as a general point, we can use analytical functions to aggregate values in a different window from the result set. Find out more.

so how do we overcome that. i mean there must be a way that you can overcome that. so that if you want to select multiple columns and just group by a particular column? — Srijan Sharma, Nov 09 '17 at 08:31

grouping while querying oracle database

1 Answers1