Group BY when having different timestamps - Oracle SQL

Question

So I have the table as you can see in the screenshot below, and I want to do a group by to the values of every month for every group. The problem is that I have different time stamps.. and this column is very necessary. Is there any way to:

avoid adding in the group by statement? or
change the day in the time stamp so all will be like the first day of the month? or
extract the quarter from the time stamp and then try to use in in the group by?

Thank you

Wanted output

The code I am using:

Select Name, Central, Group, Timestamp, Extract (year from Timestamp) as Year, Extract (month from Timestamp) From Table1 Group BY Name, Central, Group, Timestamp

I can't remove Timestamp from the Group BY .. and adding Year and Month to the group by doesn't change any thing

Did you tried group by GRUPO, to_char(TIMESTAMP, 'YYYY-MM') ? If you have timestamp which contains only year 20019 you can use extract method. — O.Man
– O.Man, Commented Jul 9, 2020 at 9:15
A GROUP BY at the month level would remove the timestamp detail. Your question is not clear. You need sample data and desired results. — Gordon Linoff
– Gordon Linoff, Commented Jul 9, 2020 at 11:19

sers · Accepted Answer · 2020-07-09 10:41:00Z

0

So I gather you need the timestamp column in your result set and you don't want to group by the timestamp. I don't know of what datatype your column TIMESTAMP is, but for simplicity I'll assume it's a date value. You could do something like this:

select grupo,year,month,min(timestamp)
  from <yourtable>
group by grupo, year,month
;

Or this to return the first day of the month:

select grupo,year,month,tunc(timestamp,'MM')
  from <yourtable>
group by grupo, year,month,tunc(timestamp,'MM')
;

Of course you could also do:

select grupo,year,month,min(trunc(timestamp,'MM'))
  from <yourtable>
group by grupo, year,month
;

answered Jul 9, 2020 at 10:41

sers

3,7794 gold badges24 silver badges30 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Group BY when having different timestamps - Oracle SQL

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related