CODEWITHSUNDEEP
sqloracle-database
StimDoker
StimDoker

Reputation: 35

Oracle - Data with one id in one line

New to sql, trying to create a query for reporting.

The following table is available:

Number NO Date
111 332 14.11.22
111 331 13.11.22
111 330 13.11.22
122 312 12.11.22
122 311 11.11.22
122 310 11.11.22

I would like to come to this view - A row in one row had a number, a maximum and a minimum NO and the corresponding dates and quantities associated with it:

Number NO min Date min NO max Date max Count
111 330 13.11.22 332 14.11.22 3
122 310 11.11.22 312 12.11.22 3

Upvotes: 0

Views: 51

Answers (1)

StimDoker
StimDoker

Reputation: 35

  with t(Num,   Start_number, Date_start) as (
select 111 , 225 , '11.11.22' from dual union all
select 111 , 223 , '9.11.22' from dual union all
select 111 , 220 , '9.11.22' from dual union all
select 222 , 347 , '11.11.22' from dual union all
select 222 , 345 , '11.11.22' from dual union all
select 222 , 343 , '10.11.22' from dual
)
select 
  Num,
  min(Start_number) as minStart,
  max(Start_number) as maxStart,
  min(Date_start) as minDate,
  max(Date_start) as maxDate,
  Count(Start_number) as cnt
from t
group by Num
order by minDate

Upvotes: 2

Related Questions