select columns without having them in the group by

Question

I have a table when the sample data is as below

Date	name	IP	In IP	Out IP	session	activity
2/9/2023	X	123	123	Null	1	in
2/9/2023	X	123	Null	123	1	out
2/8/2023	Y	143	null	143	2	out
2/8/2023	Y	153	153	null	2	in
2/8/2023	X	163	163	null	3	in
2/8/2023	X	163	null	163	3	out

By grouping by name, ip and session with date & activity. I am able to get the records needed.

select
  name,
  ip,
  max(case when activity = 'in' then date end) in
  max(case when activity = 'out' then date end) out
from view
group by name, ip, session

Generally In IP & Out IP are same.but at times like case for name=y , in IP and out ip are different so i need to get IN IP and OUT IP as well at the result.

How can use this group by and get the other two columns as well?

https://dbfiddle.uk/zzNpjP3f

Please read https://meta.stackoverflow.com/questions/277716/how-can-i-create-a-table-in-a-post and then provide both sample input data and expected result as correct formatted tables. — Jonas Metzler, Feb 09 '23 at 06:57

d r · Answer 1 · 2023-02-09T09:35:08.903

Not quite sure what exactly you want. With your sample data...

WITH
    tbl (A_DATE, A_NAME, IP, IP_IN, IP_OUT, A_SESSION, ACTIVITY) AS
        (
            Select To_Date('02/09/2023', 'mm/dd/yyyy'), 'X', 123, 123, Null, 1, 'in' From Dual Union All
            Select To_Date('02/09/2023', 'mm/dd/yyyy'), 'X', 123, Null, 123, 1, 'out' From Dual Union All
            Select To_Date('02/08/2023', 'mm/dd/yyyy'), 'Y', 143, Null, 143, 2, 'out' From Dual Union All
            Select To_Date('02/08/2023', 'mm/dd/yyyy'), 'Y', 153, 153, Null, 2, 'in' From Dual Union All
            Select To_Date('02/08/2023', 'mm/dd/yyyy'), 'X', 163, 163, Null, 3, 'in' From Dual Union All
            Select To_Date('02/08/2023', 'mm/dd/yyyy'), 'X', 163, Null, 163, 3, 'out' From Dual 
        )

... you could do a small transformation to get all the rows with some transformed columns like here..

Select
    A_NAME, IP, IP_IN, IP_OUT, A_SESSION, ACTIVITY,
    CASE WHEN ACTIVITY = 'in'  THEN IP_IN END "IP_IN",
    CASE WHEN ACTIVITY = 'in'  THEN A_DATE END "IN_DATE",
    CASE WHEN ACTIVITY = 'out' THEN IP_OUT END "IP_OUT",
    CASE WHEN ACTIVITY = 'out' THEN A_DATE END "OUT_DATE"
From 
    tbl

R e s u l t :
A_NAME         IP      IP_IN     IP_OUT  A_SESSION ACTIVITY      IP_IN IN_DATE       IP_OUT OUT_DATE
------ ---------- ---------- ---------- ---------- -------- ---------- --------- ---------- ---------
X             123        123                     1 in              123 09-FEB-23                      
X             123                   123          1 out                                  123 09-FEB-23 
Y             143                   143          2 out                                  143 08-FEB-23 
Y             153        153                     2 in              153 08-FEB-23                      
X             163        163                     3 in              163 08-FEB-23                      
X             163                   163          3 out                                  163 08-FEB-23

... with the resulting dataset you can do either aggregation with group by or you can use analytic functions to get what you want...
Option 1 - aggregate

Select
    A_NAME, IP, A_SESSION,
    MAX(CASE WHEN ACTIVITY = 'in'  THEN IP_IN END) "IP_IN",
    MAX(CASE WHEN ACTIVITY = 'in'  THEN A_DATE END) "IN_DATE",
    MAX(CASE WHEN ACTIVITY = 'out' THEN IP_OUT END) "IP_OUT",
    MAX(CASE WHEN ACTIVITY = 'out' THEN A_DATE END) "OUT_DATE"
From 
    tbl
Group By 
    A_NAME, IP, A_SESSION

A_NAME         IP  A_SESSION      IP_IN IN_DATE       IP_OUT OUT_DATE
------ ---------- ---------- ---------- --------- ---------- ---------
Y             153          2        153 08-FEB-23                      
X             163          3        163 08-FEB-23        163 08-FEB-23 
Y             143          2                             143 08-FEB-23 
X             123          1        123 09-FEB-23        123 09-FEB-23

Option 2 - aggregate

Select
    A_NAME, IP,
    MAX(CASE WHEN ACTIVITY = 'in'  THEN IP_IN END) "IP_IN",
    MAX(CASE WHEN ACTIVITY = 'in'  THEN A_DATE END) "IN_DATE",
    MAX(CASE WHEN ACTIVITY = 'out' THEN IP_OUT END) "IP_OUT",
    MAX(CASE WHEN ACTIVITY = 'out' THEN A_DATE END) "OUT_DATE"
From 
    tbl
Group By 
    A_NAME, IP

A_NAME         IP      IP_IN IN_DATE       IP_OUT OUT_DATE
------ ---------- ---------- --------- ---------- ---------
X             163        163 08-FEB-23        163 08-FEB-23 
X             123        123 09-FEB-23        123 09-FEB-23 
Y             153        153 08-FEB-23                      
Y             143                             143 08-FEB-23

Option 2A - IN_DATE as VARCHAR2 (from comments)

Select  A_NAME, IP, 
        IP_IN, CASE WHEN IN_DATE IS NULL THEN 'SESSION CLOSED' ELSE To_Char(IN_DATE, 'dd.mm.yyyy') END "IN_DATE",
        IP_OUT, OUT_DATE
From
    (
        Select
            A_NAME, IP,
            MAX(CASE WHEN ACTIVITY = 'in'  THEN IP_IN END) "IP_IN",
            MAX(CASE WHEN ACTIVITY = 'in'  THEN A_DATE END) "IN_DATE",
            MAX(CASE WHEN ACTIVITY = 'out' THEN IP_OUT END) "IP_OUT",
            MAX(CASE WHEN ACTIVITY = 'out' THEN A_DATE END) "OUT_DATE"
        From 
            tbl
        Group By 
            A_NAME, IP
    )

A_NAME         IP      IP_IN IN_DATE            IP_OUT OUT_DATE
------ ---------- ---------- -------------- ---------- ---------
X             163        163 08.02.2023            163 08-FEB-23 
X             123        123 09.02.2023            123 09-FEB-23 
Y             153        153 08.02.2023                          
Y             143            SESSION CLOSED        143 08-FEB-23

Option 3 - analytic

Select
    A_NAME, IP, A_SESSION,
    CASE WHEN ACTIVITY = 'in'  THEN IP_IN END "MAX_IP_IN",
    MAX(CASE WHEN ACTIVITY = 'in'  THEN A_DATE END) OVER(Partition By A_NAME, IP) "IN_DATE",
    CASE WHEN ACTIVITY = 'out' THEN IP_OUT END "MAAX_IP_OUT",
    MAX(CASE WHEN ACTIVITY = 'out' THEN A_DATE END) OVER(Partition By A_NAME, IP) "OUT_DATE"
From 
    tbl


A_NAME         IP  A_SESSION  MAX_IP_IN IN_DATE   MAAX_IP_OUT OUT_DATE
------ ---------- ---------- ---------- --------- ----------- ---------
X             123          1        123 09-FEB-23             09-FEB-23 
X             123          1            09-FEB-23         123 09-FEB-23 
X             163          3            08-FEB-23         163 08-FEB-23 
X             163          3        163 08-FEB-23             08-FEB-23 
Y             143          2                              143 08-FEB-23 
Y             153          2        153 08-FEB-23

Option 4 - analytic

Select
    A_NAME, IP, A_SESSION, 
    CASE WHEN ACTIVITY = 'in'  THEN IP_IN END "MAX_IP_IN",
    MAX(CASE WHEN ACTIVITY = 'in'  THEN A_DATE END) OVER(Partition By A_NAME, IP, ACTIVITY) "IN_DATE",
    CASE WHEN ACTIVITY = 'out' THEN IP_OUT END "MAAX_IP_OUT",
    MAX(CASE WHEN ACTIVITY = 'out' THEN A_DATE END) OVER(Partition By A_NAME, IP, ACTIVITY) "OUT_DATE"
From 
    tbl


A_NAME         IP  A_SESSION  MAX_IP_IN IN_DATE   MAAX_IP_OUT OUT_DATE
------ ---------- ---------- ---------- --------- ----------- ---------
X             123          1        123 09-FEB-23                       
X             123          1                              123 09-FEB-23 
X             163          3        163 08-FEB-23                       
X             163          3                              163 08-FEB-23 
Y             143          2                              143 08-FEB-23 
Y             153          2        153 08-FEB-23

... and so on... using (maybe) different functions not just MAX() (aggregate or analytic) with different Group By / Partition By

Thanks. In Option 2, If the out date is null, I want to update the column as 'session closed' like MAX(CASE WHEN ACTIVITY = 'in' THEN CASE WHEN( A_DATE IS NOT NULL) THEN A_DATE ELSE 'SESSION CLOSED' END) "IN_DATE" will it work in this way — nav, Feb 09 '23 at 08:26
@nav No, the CASE expression THEN and ELSE should have the same datatype. You can't return DATE with THEN and VARCHAR2 with ELSE... If it is just the Select sql (not writing into table) you could select it as CASE ... THEN To_Char(A_DATE) ELSE 'some text' .... This way, both, THEN and ELSE would return VARCHAR2 — d r, Feb 09 '23 at 08:57
@d r Thanks. I have tried to insert the records into a table and then ran an update query to update the column with session closed when it is null. This works . Is there a way i can make this happen with the query you provided in option 2. — nav, Feb 09 '23 at 09:05
@nav If you are writing something in a table column the value have to be of the same datatype. Meaning - if your table column is of DATE datatype then it is not posible to write 'SESSION CLOSED' into that column. If the table column is of VARCHAR2 datatype you can write 'SESSION CLOSED' or a date converted to char '08-FEB-23' but not a date of DATE datatype. The last case will work but the dates will be written as VARCHAR2 (not as DATE datatype). — d r, Feb 09 '23 at 09:13
@nav Just posted option 2A... Intentionaly formated dates differently (as VARCHAR2) — d r, Feb 09 '23 at 09:28

score 0 · Answer 2 · answered Feb 09 '23 at 07:19

Right now you have a result row per name, ip, and session (GROUP BY name, ip, session). From your explanation I gather that you rather want a result row per name, and session only (GROUP BY name, session).

As to getting the IN/OUT IPs, use the same method as for the IN/OUT dates:

select
  name,
  max(case when activity = 'in' then date end) as in_date,
  max(case when activity = 'out' then date end) as out_date,
  max(case when activity = 'in' then in_ip end) as in_ip,
  max(case when activity = 'out' then out_ip end) as out_ip
from view
group by name, session
order by name, session;

Thanks.. I have another query. If there is no record for out ip then out_date will be null. I dont want to keep the out date as null..If the outdat is null, then it should update the column as session_closed please see https://dbfiddle.uk/Ei7io9fr — nav, Feb 09 '23 at 07:53

score 0 · Answer 3 · answered Feb 09 '23 at 08:19

0

Well, you may use max, min, sum, avg and other group functions with the clause over (partition by ...). See: MAX() OVER PARTITION BY in Oracle SQL https://forums.oracle.com/ords/apexds/post/max-from-multiple-column-and-group-by-8269

answered Feb 09 '23 at 08:19

Bogdan Dincescu

683
6

score 0 · Answer 4 · answered Feb 09 '23 at 10:58

Assuming that you are looking for in/out pairs of of entries on a day then, from Oracle 12, you an use MATCH_RECOGNIZE for row-by-row pattern matching:

SELECT user_1, in_ip, in_time, out_ip, out_time
FROM   (
  SELECT v.*,
         TRUNC(date_column) AS day
  FROM   view_table v
)
MATCH_RECOGNIZE(
  PARTITION BY user_1, day
  ORDER     BY date_column
  MEASURES
    user_in.ip           AS in_ip,
    user_in.date_column  AS in_time,
    user_out.ip          AS out_ip,
    user_out.date_column AS out_time
  PATTERN ( user_in user_out? | user_out user_in? )
  DEFINE
    user_in  AS activity = 'IN',
    user_out AS activity = 'OUT'
)

Which, for the sample data:

CREATE TABLE view_TABLE (date_column, user_1,ip, in_ip,out_ip,session_1,activity) AS
SELECT  DATE '2023-02-09' + INTERVAL '15:06' HOUR TO MINUTE, 'X', 123, 123,  NULL, 1, 'IN'  FROM DUAL UNION ALL
SELECT  DATE '2023-02-09' + INTERVAL '15:08' HOUR TO MINUTE, 'X', 123, NULL, 123,  1, 'OUT' FROM DUAL UNION ALL
SELECT  DATE '2023-02-08' + INTERVAL '16:08' HOUR TO MINUTE, 'Y', 143, NULL, 143,  2, 'OUT' FROM DUAL UNION ALL
SELECT  DATE '2023-02-08' + INTERVAL '16:04' HOUR TO MINUTE, 'Y', 153, 153,  NULL, 2, 'IN'  FROM DUAL UNION ALL
SELECT  DATE '2023-02-08' + INTERVAL '12:45' HOUR TO MINUTE, 'X', 163, 163,  NULL, 3, 'IN'  FROM DUAL UNION ALL
SELECT  DATE '2023-02-08' + INTERVAL '12:48' HOUR TO MINUTE, 'X', 163, NULL, 163,  3, 'OUT' FROM DUAL;

Outputs:

USER_1	IN_IP	IN_TIME	OUT_IP	OUT_TIME
X	163	2023-02-08 12:45:00	163	2023-02-08 12:48:00
X	123	2023-02-09 15:06:00	123	2023-02-09 15:08:00
Y	153	2023-02-08 16:04:00	143	2023-02-08 16:08:00

fiddle

select columns without having them in the group by

4 Answers4